1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14
  15import datetime
  16import math
  17import numbers
  18import re
  19import textwrap
  20import typing as t
  21from collections import deque
  22from copy import deepcopy
  23from enum import auto
  24from functools import reduce
  25
  26from sqlglot.errors import ErrorLevel, ParseError
  27from sqlglot.helper import (
  28    AutoName,
  29    camel_to_snake_case,
  30    ensure_collection,
  31    ensure_list,
  32    is_int,
  33    seq_get,
  34    subclasses,
  35)
  36from sqlglot.tokens import Token
  37
  38if t.TYPE_CHECKING:
  39    from sqlglot._typing import E, Lit
  40    from sqlglot.dialects.dialect import DialectType
  41
  42    Q = t.TypeVar("Q", bound="Query")
  43
  44
  45class _Expression(type):
  46    def __new__(cls, clsname, bases, attrs):
  47        klass = super().__new__(cls, clsname, bases, attrs)
  48
  49        # When an Expression class is created, its key is automatically set to be
  50        # the lowercase version of the class' name.
  51        klass.key = clsname.lower()
  52
  53        # This is so that docstrings are not inherited in pdoc
  54        klass.__doc__ = klass.__doc__ or ""
  55
  56        return klass
  57
  58
  59SQLGLOT_META = "sqlglot.meta"
  60TABLE_PARTS = ("this", "db", "catalog")
  61
  62
  63class Expression(metaclass=_Expression):
  64    """
  65    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  66    context, such as its child expressions, their names (arg keys), and whether a given child expression
  67    is optional or not.
  68
  69    Attributes:
  70        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  71            and representing expressions as strings.
  72        arg_types: determines the arguments (child nodes) supported by an expression. It maps
  73            arg keys to booleans that indicate whether the corresponding args are optional.
  74        parent: a reference to the parent expression (or None, in case of root expressions).
  75        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  76            uses to refer to it.
  77        index: the index of an expression if it is inside of a list argument in its parent.
  78        comments: a list of comments that are associated with a given expression. This is used in
  79            order to preserve comments when transpiling SQL code.
  80        type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  81            optimizer, in order to enable some transformations that require type information.
  82        meta: a dictionary that can be used to store useful metadata for a given expression.
  83
  84    Example:
  85        >>> class Foo(Expression):
  86        ...     arg_types = {"this": True, "expression": False}
  87
  88        The above definition informs us that Foo is an Expression that requires an argument called
  89        "this" and may also optionally receive an argument called "expression".
  90
  91    Args:
  92        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  93    """
  94
  95    key = "expression"
  96    arg_types = {"this": True}
  97    __slots__ = ("args", "parent", "arg_key", "index", "comments", "_type", "_meta", "_hash")
  98
  99    def __init__(self, **args: t.Any):
 100        self.args: t.Dict[str, t.Any] = args
 101        self.parent: t.Optional[Expression] = None
 102        self.arg_key: t.Optional[str] = None
 103        self.index: t.Optional[int] = None
 104        self.comments: t.Optional[t.List[str]] = None
 105        self._type: t.Optional[DataType] = None
 106        self._meta: t.Optional[t.Dict[str, t.Any]] = None
 107        self._hash: t.Optional[int] = None
 108
 109        for arg_key, value in self.args.items():
 110            self._set_parent(arg_key, value)
 111
 112    def __eq__(self, other) -> bool:
 113        return type(self) is type(other) and hash(self) == hash(other)
 114
 115    @property
 116    def hashable_args(self) -> t.Any:
 117        return frozenset(
 118            (k, tuple(_norm_arg(a) for a in v) if type(v) is list else _norm_arg(v))
 119            for k, v in self.args.items()
 120            if not (v is None or v is False or (type(v) is list and not v))
 121        )
 122
 123    def __hash__(self) -> int:
 124        if self._hash is not None:
 125            return self._hash
 126
 127        return hash((self.__class__, self.hashable_args))
 128
 129    @property
 130    def this(self) -> t.Any:
 131        """
 132        Retrieves the argument with key "this".
 133        """
 134        return self.args.get("this")
 135
 136    @property
 137    def expression(self) -> t.Any:
 138        """
 139        Retrieves the argument with key "expression".
 140        """
 141        return self.args.get("expression")
 142
 143    @property
 144    def expressions(self) -> t.List[t.Any]:
 145        """
 146        Retrieves the argument with key "expressions".
 147        """
 148        return self.args.get("expressions") or []
 149
 150    def text(self, key) -> str:
 151        """
 152        Returns a textual representation of the argument corresponding to "key". This can only be used
 153        for args that are strings or leaf Expression instances, such as identifiers and literals.
 154        """
 155        field = self.args.get(key)
 156        if isinstance(field, str):
 157            return field
 158        if isinstance(field, (Identifier, Literal, Var)):
 159            return field.this
 160        if isinstance(field, (Star, Null)):
 161            return field.name
 162        return ""
 163
 164    @property
 165    def is_string(self) -> bool:
 166        """
 167        Checks whether a Literal expression is a string.
 168        """
 169        return isinstance(self, Literal) and self.args["is_string"]
 170
 171    @property
 172    def is_number(self) -> bool:
 173        """
 174        Checks whether a Literal expression is a number.
 175        """
 176        return isinstance(self, Literal) and not self.args["is_string"]
 177
 178    @property
 179    def is_int(self) -> bool:
 180        """
 181        Checks whether a Literal expression is an integer.
 182        """
 183        return self.is_number and is_int(self.name)
 184
 185    @property
 186    def is_star(self) -> bool:
 187        """Checks whether an expression is a star."""
 188        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 189
 190    @property
 191    def alias(self) -> str:
 192        """
 193        Returns the alias of the expression, or an empty string if it's not aliased.
 194        """
 195        if isinstance(self.args.get("alias"), TableAlias):
 196            return self.args["alias"].name
 197        return self.text("alias")
 198
 199    @property
 200    def alias_column_names(self) -> t.List[str]:
 201        table_alias = self.args.get("alias")
 202        if not table_alias:
 203            return []
 204        return [c.name for c in table_alias.args.get("columns") or []]
 205
 206    @property
 207    def name(self) -> str:
 208        return self.text("this")
 209
 210    @property
 211    def alias_or_name(self) -> str:
 212        return self.alias or self.name
 213
 214    @property
 215    def output_name(self) -> str:
 216        """
 217        Name of the output column if this expression is a selection.
 218
 219        If the Expression has no output name, an empty string is returned.
 220
 221        Example:
 222            >>> from sqlglot import parse_one
 223            >>> parse_one("SELECT a").expressions[0].output_name
 224            'a'
 225            >>> parse_one("SELECT b AS c").expressions[0].output_name
 226            'c'
 227            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 228            ''
 229        """
 230        return ""
 231
 232    @property
 233    def type(self) -> t.Optional[DataType]:
 234        return self._type
 235
 236    @type.setter
 237    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 238        if dtype and not isinstance(dtype, DataType):
 239            dtype = DataType.build(dtype)
 240        self._type = dtype  # type: ignore
 241
 242    def is_type(self, *dtypes) -> bool:
 243        return self.type is not None and self.type.is_type(*dtypes)
 244
 245    def is_leaf(self) -> bool:
 246        return not any(isinstance(v, (Expression, list)) for v in self.args.values())
 247
 248    @property
 249    def meta(self) -> t.Dict[str, t.Any]:
 250        if self._meta is None:
 251            self._meta = {}
 252        return self._meta
 253
 254    def __deepcopy__(self, memo):
 255        root = self.__class__()
 256        stack = [(self, root)]
 257
 258        while stack:
 259            node, copy = stack.pop()
 260
 261            if node.comments is not None:
 262                copy.comments = deepcopy(node.comments)
 263            if node._type is not None:
 264                copy._type = deepcopy(node._type)
 265            if node._meta is not None:
 266                copy._meta = deepcopy(node._meta)
 267            if node._hash is not None:
 268                copy._hash = node._hash
 269
 270            for k, vs in node.args.items():
 271                if hasattr(vs, "parent"):
 272                    stack.append((vs, vs.__class__()))
 273                    copy.set(k, stack[-1][-1])
 274                elif type(vs) is list:
 275                    copy.args[k] = []
 276
 277                    for v in vs:
 278                        if hasattr(v, "parent"):
 279                            stack.append((v, v.__class__()))
 280                            copy.append(k, stack[-1][-1])
 281                        else:
 282                            copy.append(k, v)
 283                else:
 284                    copy.args[k] = vs
 285
 286        return root
 287
 288    def copy(self):
 289        """
 290        Returns a deep copy of the expression.
 291        """
 292        return deepcopy(self)
 293
 294    def add_comments(self, comments: t.Optional[t.List[str]]) -> None:
 295        if self.comments is None:
 296            self.comments = []
 297        if comments:
 298            for comment in comments:
 299                _, *meta = comment.split(SQLGLOT_META)
 300                if meta:
 301                    for kv in "".join(meta).split(","):
 302                        k, *v = kv.split("=")
 303                        value = v[0].strip() if v else True
 304                        self.meta[k.strip()] = value
 305                self.comments.append(comment)
 306
 307    def append(self, arg_key: str, value: t.Any) -> None:
 308        """
 309        Appends value to arg_key if it's a list or sets it as a new list.
 310
 311        Args:
 312            arg_key (str): name of the list expression arg
 313            value (Any): value to append to the list
 314        """
 315        if type(self.args.get(arg_key)) is not list:
 316            self.args[arg_key] = []
 317        self._set_parent(arg_key, value)
 318        values = self.args[arg_key]
 319        if hasattr(value, "parent"):
 320            value.index = len(values)
 321        values.append(value)
 322
 323    def set(self, arg_key: str, value: t.Any, index: t.Optional[int] = None) -> None:
 324        """
 325        Sets arg_key to value.
 326
 327        Args:
 328            arg_key: name of the expression arg.
 329            value: value to set the arg to.
 330            index: if the arg is a list, this specifies what position to add the value in it.
 331        """
 332        if index is not None:
 333            expressions = self.args.get(arg_key) or []
 334
 335            if seq_get(expressions, index) is None:
 336                return
 337            if value is None:
 338                expressions.pop(index)
 339                for v in expressions[index:]:
 340                    v.index = v.index - 1
 341                return
 342
 343            if isinstance(value, list):
 344                expressions.pop(index)
 345                expressions[index:index] = value
 346            else:
 347                expressions[index] = value
 348
 349            value = expressions
 350        elif value is None:
 351            self.args.pop(arg_key, None)
 352            return
 353
 354        self.args[arg_key] = value
 355        self._set_parent(arg_key, value, index)
 356
 357    def _set_parent(self, arg_key: str, value: t.Any, index: t.Optional[int] = None) -> None:
 358        if hasattr(value, "parent"):
 359            value.parent = self
 360            value.arg_key = arg_key
 361            value.index = index
 362        elif type(value) is list:
 363            for index, v in enumerate(value):
 364                if hasattr(v, "parent"):
 365                    v.parent = self
 366                    v.arg_key = arg_key
 367                    v.index = index
 368
 369    @property
 370    def depth(self) -> int:
 371        """
 372        Returns the depth of this tree.
 373        """
 374        if self.parent:
 375            return self.parent.depth + 1
 376        return 0
 377
 378    def iter_expressions(self, reverse: bool = False) -> t.Iterator[Expression]:
 379        """Yields the key and expression for all arguments, exploding list args."""
 380        # remove tuple when python 3.7 is deprecated
 381        for vs in reversed(tuple(self.args.values())) if reverse else self.args.values():
 382            if type(vs) is list:
 383                for v in reversed(vs) if reverse else vs:
 384                    if hasattr(v, "parent"):
 385                        yield v
 386            else:
 387                if hasattr(vs, "parent"):
 388                    yield vs
 389
 390    def find(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Optional[E]:
 391        """
 392        Returns the first node in this tree which matches at least one of
 393        the specified types.
 394
 395        Args:
 396            expression_types: the expression type(s) to match.
 397            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 398
 399        Returns:
 400            The node which matches the criteria or None if no such node was found.
 401        """
 402        return next(self.find_all(*expression_types, bfs=bfs), None)
 403
 404    def find_all(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Iterator[E]:
 405        """
 406        Returns a generator object which visits all nodes in this tree and only
 407        yields those that match at least one of the specified expression types.
 408
 409        Args:
 410            expression_types: the expression type(s) to match.
 411            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 412
 413        Returns:
 414            The generator object.
 415        """
 416        for expression in self.walk(bfs=bfs):
 417            if isinstance(expression, expression_types):
 418                yield expression
 419
 420    def find_ancestor(self, *expression_types: t.Type[E]) -> t.Optional[E]:
 421        """
 422        Returns a nearest parent matching expression_types.
 423
 424        Args:
 425            expression_types: the expression type(s) to match.
 426
 427        Returns:
 428            The parent node.
 429        """
 430        ancestor = self.parent
 431        while ancestor and not isinstance(ancestor, expression_types):
 432            ancestor = ancestor.parent
 433        return ancestor  # type: ignore
 434
 435    @property
 436    def parent_select(self) -> t.Optional[Select]:
 437        """
 438        Returns the parent select statement.
 439        """
 440        return self.find_ancestor(Select)
 441
 442    @property
 443    def same_parent(self) -> bool:
 444        """Returns if the parent is the same class as itself."""
 445        return type(self.parent) is self.__class__
 446
 447    def root(self) -> Expression:
 448        """
 449        Returns the root expression of this tree.
 450        """
 451        expression = self
 452        while expression.parent:
 453            expression = expression.parent
 454        return expression
 455
 456    def walk(
 457        self, bfs: bool = True, prune: t.Optional[t.Callable[[Expression], bool]] = None
 458    ) -> t.Iterator[Expression]:
 459        """
 460        Returns a generator object which visits all nodes in this tree.
 461
 462        Args:
 463            bfs: if set to True the BFS traversal order will be applied,
 464                otherwise the DFS traversal will be used instead.
 465            prune: callable that returns True if the generator should stop traversing
 466                this branch of the tree.
 467
 468        Returns:
 469            the generator object.
 470        """
 471        if bfs:
 472            yield from self.bfs(prune=prune)
 473        else:
 474            yield from self.dfs(prune=prune)
 475
 476    def dfs(
 477        self, prune: t.Optional[t.Callable[[Expression], bool]] = None
 478    ) -> t.Iterator[Expression]:
 479        """
 480        Returns a generator object which visits all nodes in this tree in
 481        the DFS (Depth-first) order.
 482
 483        Returns:
 484            The generator object.
 485        """
 486        stack = [self]
 487
 488        while stack:
 489            node = stack.pop()
 490
 491            yield node
 492
 493            if prune and prune(node):
 494                continue
 495
 496            for v in node.iter_expressions(reverse=True):
 497                stack.append(v)
 498
 499    def bfs(
 500        self, prune: t.Optional[t.Callable[[Expression], bool]] = None
 501    ) -> t.Iterator[Expression]:
 502        """
 503        Returns a generator object which visits all nodes in this tree in
 504        the BFS (Breadth-first) order.
 505
 506        Returns:
 507            The generator object.
 508        """
 509        queue = deque([self])
 510
 511        while queue:
 512            node = queue.popleft()
 513
 514            yield node
 515
 516            if prune and prune(node):
 517                continue
 518
 519            for v in node.iter_expressions():
 520                queue.append(v)
 521
 522    def unnest(self):
 523        """
 524        Returns the first non parenthesis child or self.
 525        """
 526        expression = self
 527        while type(expression) is Paren:
 528            expression = expression.this
 529        return expression
 530
 531    def unalias(self):
 532        """
 533        Returns the inner expression if this is an Alias.
 534        """
 535        if isinstance(self, Alias):
 536            return self.this
 537        return self
 538
 539    def unnest_operands(self):
 540        """
 541        Returns unnested operands as a tuple.
 542        """
 543        return tuple(arg.unnest() for arg in self.iter_expressions())
 544
 545    def flatten(self, unnest=True):
 546        """
 547        Returns a generator which yields child nodes whose parents are the same class.
 548
 549        A AND B AND C -> [A, B, C]
 550        """
 551        for node in self.dfs(prune=lambda n: n.parent and type(n) is not self.__class__):
 552            if type(node) is not self.__class__:
 553                yield node.unnest() if unnest and not isinstance(node, Subquery) else node
 554
 555    def __str__(self) -> str:
 556        return self.sql()
 557
 558    def __repr__(self) -> str:
 559        return _to_s(self)
 560
 561    def to_s(self) -> str:
 562        """
 563        Same as __repr__, but includes additional information which can be useful
 564        for debugging, like empty or missing args and the AST nodes' object IDs.
 565        """
 566        return _to_s(self, verbose=True)
 567
 568    def sql(self, dialect: DialectType = None, **opts) -> str:
 569        """
 570        Returns SQL string representation of this tree.
 571
 572        Args:
 573            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 574            opts: other `sqlglot.generator.Generator` options.
 575
 576        Returns:
 577            The SQL string.
 578        """
 579        from sqlglot.dialects import Dialect
 580
 581        return Dialect.get_or_raise(dialect).generate(self, **opts)
 582
 583    def transform(self, fun: t.Callable, *args: t.Any, copy: bool = True, **kwargs) -> Expression:
 584        """
 585        Visits all tree nodes (excluding already transformed ones)
 586        and applies the given transformation function to each node.
 587
 588        Args:
 589            fun (function): a function which takes a node as an argument and returns a
 590                new transformed node or the same node without modifications. If the function
 591                returns None, then the corresponding node will be removed from the syntax tree.
 592            copy (bool): if set to True a new tree instance is constructed, otherwise the tree is
 593                modified in place.
 594
 595        Returns:
 596            The transformed tree.
 597        """
 598        root = None
 599        new_node = None
 600
 601        for node in (self.copy() if copy else self).dfs(prune=lambda n: n is not new_node):
 602            parent, arg_key, index = node.parent, node.arg_key, node.index
 603            new_node = fun(node, *args, **kwargs)
 604
 605            if not root:
 606                root = new_node
 607            elif new_node is not node:
 608                parent.set(arg_key, new_node, index)
 609
 610        assert root
 611        return root.assert_is(Expression)
 612
 613    @t.overload
 614    def replace(self, expression: E) -> E: ...
 615
 616    @t.overload
 617    def replace(self, expression: None) -> None: ...
 618
 619    def replace(self, expression):
 620        """
 621        Swap out this expression with a new expression.
 622
 623        For example::
 624
 625            >>> tree = Select().select("x").from_("tbl")
 626            >>> tree.find(Column).replace(column("y"))
 627            Column(
 628              this=Identifier(this=y, quoted=False))
 629            >>> tree.sql()
 630            'SELECT y FROM tbl'
 631
 632        Args:
 633            expression: new node
 634
 635        Returns:
 636            The new expression or expressions.
 637        """
 638        parent = self.parent
 639
 640        if not parent or parent is expression:
 641            return expression
 642
 643        key = self.arg_key
 644        value = parent.args.get(key)
 645
 646        if type(expression) is list and isinstance(value, Expression):
 647            # We are trying to replace an Expression with a list, so it's assumed that
 648            # the intention was to really replace the parent of this expression.
 649            value.parent.replace(expression)
 650        else:
 651            parent.set(key, expression, self.index)
 652
 653        if expression is not self:
 654            self.parent = None
 655            self.arg_key = None
 656            self.index = None
 657
 658        return expression
 659
 660    def pop(self: E) -> E:
 661        """
 662        Remove this expression from its AST.
 663
 664        Returns:
 665            The popped expression.
 666        """
 667        self.replace(None)
 668        return self
 669
 670    def assert_is(self, type_: t.Type[E]) -> E:
 671        """
 672        Assert that this `Expression` is an instance of `type_`.
 673
 674        If it is NOT an instance of `type_`, this raises an assertion error.
 675        Otherwise, this returns this expression.
 676
 677        Examples:
 678            This is useful for type security in chained expressions:
 679
 680            >>> import sqlglot
 681            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 682            'SELECT x, z FROM y'
 683        """
 684        if not isinstance(self, type_):
 685            raise AssertionError(f"{self} is not {type_}.")
 686        return self
 687
 688    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 689        """
 690        Checks if this expression is valid (e.g. all mandatory args are set).
 691
 692        Args:
 693            args: a sequence of values that were used to instantiate a Func expression. This is used
 694                to check that the provided arguments don't exceed the function argument limit.
 695
 696        Returns:
 697            A list of error messages for all possible errors that were found.
 698        """
 699        errors: t.List[str] = []
 700
 701        for k in self.args:
 702            if k not in self.arg_types:
 703                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 704        for k, mandatory in self.arg_types.items():
 705            v = self.args.get(k)
 706            if mandatory and (v is None or (isinstance(v, list) and not v)):
 707                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 708
 709        if (
 710            args
 711            and isinstance(self, Func)
 712            and len(args) > len(self.arg_types)
 713            and not self.is_var_len_args
 714        ):
 715            errors.append(
 716                f"The number of provided arguments ({len(args)}) is greater than "
 717                f"the maximum number of supported arguments ({len(self.arg_types)})"
 718            )
 719
 720        return errors
 721
 722    def dump(self):
 723        """
 724        Dump this Expression to a JSON-serializable dict.
 725        """
 726        from sqlglot.serde import dump
 727
 728        return dump(self)
 729
 730    @classmethod
 731    def load(cls, obj):
 732        """
 733        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 734        """
 735        from sqlglot.serde import load
 736
 737        return load(obj)
 738
 739    def and_(
 740        self,
 741        *expressions: t.Optional[ExpOrStr],
 742        dialect: DialectType = None,
 743        copy: bool = True,
 744        **opts,
 745    ) -> Condition:
 746        """
 747        AND this condition with one or multiple expressions.
 748
 749        Example:
 750            >>> condition("x=1").and_("y=1").sql()
 751            'x = 1 AND y = 1'
 752
 753        Args:
 754            *expressions: the SQL code strings to parse.
 755                If an `Expression` instance is passed, it will be used as-is.
 756            dialect: the dialect used to parse the input expression.
 757            copy: whether to copy the involved expressions (only applies to Expressions).
 758            opts: other options to use to parse the input expressions.
 759
 760        Returns:
 761            The new And condition.
 762        """
 763        return and_(self, *expressions, dialect=dialect, copy=copy, **opts)
 764
 765    def or_(
 766        self,
 767        *expressions: t.Optional[ExpOrStr],
 768        dialect: DialectType = None,
 769        copy: bool = True,
 770        **opts,
 771    ) -> Condition:
 772        """
 773        OR this condition with one or multiple expressions.
 774
 775        Example:
 776            >>> condition("x=1").or_("y=1").sql()
 777            'x = 1 OR y = 1'
 778
 779        Args:
 780            *expressions: the SQL code strings to parse.
 781                If an `Expression` instance is passed, it will be used as-is.
 782            dialect: the dialect used to parse the input expression.
 783            copy: whether to copy the involved expressions (only applies to Expressions).
 784            opts: other options to use to parse the input expressions.
 785
 786        Returns:
 787            The new Or condition.
 788        """
 789        return or_(self, *expressions, dialect=dialect, copy=copy, **opts)
 790
 791    def not_(self, copy: bool = True):
 792        """
 793        Wrap this condition with NOT.
 794
 795        Example:
 796            >>> condition("x=1").not_().sql()
 797            'NOT x = 1'
 798
 799        Args:
 800            copy: whether to copy this object.
 801
 802        Returns:
 803            The new Not instance.
 804        """
 805        return not_(self, copy=copy)
 806
 807    def as_(
 808        self,
 809        alias: str | Identifier,
 810        quoted: t.Optional[bool] = None,
 811        dialect: DialectType = None,
 812        copy: bool = True,
 813        **opts,
 814    ) -> Alias:
 815        return alias_(self, alias, quoted=quoted, dialect=dialect, copy=copy, **opts)
 816
 817    def _binop(self, klass: t.Type[E], other: t.Any, reverse: bool = False) -> E:
 818        this = self.copy()
 819        other = convert(other, copy=True)
 820        if not isinstance(this, klass) and not isinstance(other, klass):
 821            this = _wrap(this, Binary)
 822            other = _wrap(other, Binary)
 823        if reverse:
 824            return klass(this=other, expression=this)
 825        return klass(this=this, expression=other)
 826
 827    def __getitem__(self, other: ExpOrStr | t.Tuple[ExpOrStr]) -> Bracket:
 828        return Bracket(
 829            this=self.copy(), expressions=[convert(e, copy=True) for e in ensure_list(other)]
 830        )
 831
 832    def __iter__(self) -> t.Iterator:
 833        if "expressions" in self.arg_types:
 834            return iter(self.args.get("expressions") or [])
 835        # We define this because __getitem__ converts Expression into an iterable, which is
 836        # problematic because one can hit infinite loops if they do "for x in some_expr: ..."
 837        # See: https://peps.python.org/pep-0234/
 838        raise TypeError(f"'{self.__class__.__name__}' object is not iterable")
 839
 840    def isin(
 841        self,
 842        *expressions: t.Any,
 843        query: t.Optional[ExpOrStr] = None,
 844        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
 845        copy: bool = True,
 846        **opts,
 847    ) -> In:
 848        return In(
 849            this=maybe_copy(self, copy),
 850            expressions=[convert(e, copy=copy) for e in expressions],
 851            query=maybe_parse(query, copy=copy, **opts) if query else None,
 852            unnest=(
 853                Unnest(
 854                    expressions=[
 855                        maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts)
 856                        for e in ensure_list(unnest)
 857                    ]
 858                )
 859                if unnest
 860                else None
 861            ),
 862        )
 863
 864    def between(self, low: t.Any, high: t.Any, copy: bool = True, **opts) -> Between:
 865        return Between(
 866            this=maybe_copy(self, copy),
 867            low=convert(low, copy=copy, **opts),
 868            high=convert(high, copy=copy, **opts),
 869        )
 870
 871    def is_(self, other: ExpOrStr) -> Is:
 872        return self._binop(Is, other)
 873
 874    def like(self, other: ExpOrStr) -> Like:
 875        return self._binop(Like, other)
 876
 877    def ilike(self, other: ExpOrStr) -> ILike:
 878        return self._binop(ILike, other)
 879
 880    def eq(self, other: t.Any) -> EQ:
 881        return self._binop(EQ, other)
 882
 883    def neq(self, other: t.Any) -> NEQ:
 884        return self._binop(NEQ, other)
 885
 886    def rlike(self, other: ExpOrStr) -> RegexpLike:
 887        return self._binop(RegexpLike, other)
 888
 889    def div(self, other: ExpOrStr, typed: bool = False, safe: bool = False) -> Div:
 890        div = self._binop(Div, other)
 891        div.args["typed"] = typed
 892        div.args["safe"] = safe
 893        return div
 894
 895    def asc(self, nulls_first: bool = True) -> Ordered:
 896        return Ordered(this=self.copy(), nulls_first=nulls_first)
 897
 898    def desc(self, nulls_first: bool = False) -> Ordered:
 899        return Ordered(this=self.copy(), desc=True, nulls_first=nulls_first)
 900
 901    def __lt__(self, other: t.Any) -> LT:
 902        return self._binop(LT, other)
 903
 904    def __le__(self, other: t.Any) -> LTE:
 905        return self._binop(LTE, other)
 906
 907    def __gt__(self, other: t.Any) -> GT:
 908        return self._binop(GT, other)
 909
 910    def __ge__(self, other: t.Any) -> GTE:
 911        return self._binop(GTE, other)
 912
 913    def __add__(self, other: t.Any) -> Add:
 914        return self._binop(Add, other)
 915
 916    def __radd__(self, other: t.Any) -> Add:
 917        return self._binop(Add, other, reverse=True)
 918
 919    def __sub__(self, other: t.Any) -> Sub:
 920        return self._binop(Sub, other)
 921
 922    def __rsub__(self, other: t.Any) -> Sub:
 923        return self._binop(Sub, other, reverse=True)
 924
 925    def __mul__(self, other: t.Any) -> Mul:
 926        return self._binop(Mul, other)
 927
 928    def __rmul__(self, other: t.Any) -> Mul:
 929        return self._binop(Mul, other, reverse=True)
 930
 931    def __truediv__(self, other: t.Any) -> Div:
 932        return self._binop(Div, other)
 933
 934    def __rtruediv__(self, other: t.Any) -> Div:
 935        return self._binop(Div, other, reverse=True)
 936
 937    def __floordiv__(self, other: t.Any) -> IntDiv:
 938        return self._binop(IntDiv, other)
 939
 940    def __rfloordiv__(self, other: t.Any) -> IntDiv:
 941        return self._binop(IntDiv, other, reverse=True)
 942
 943    def __mod__(self, other: t.Any) -> Mod:
 944        return self._binop(Mod, other)
 945
 946    def __rmod__(self, other: t.Any) -> Mod:
 947        return self._binop(Mod, other, reverse=True)
 948
 949    def __pow__(self, other: t.Any) -> Pow:
 950        return self._binop(Pow, other)
 951
 952    def __rpow__(self, other: t.Any) -> Pow:
 953        return self._binop(Pow, other, reverse=True)
 954
 955    def __and__(self, other: t.Any) -> And:
 956        return self._binop(And, other)
 957
 958    def __rand__(self, other: t.Any) -> And:
 959        return self._binop(And, other, reverse=True)
 960
 961    def __or__(self, other: t.Any) -> Or:
 962        return self._binop(Or, other)
 963
 964    def __ror__(self, other: t.Any) -> Or:
 965        return self._binop(Or, other, reverse=True)
 966
 967    def __neg__(self) -> Neg:
 968        return Neg(this=_wrap(self.copy(), Binary))
 969
 970    def __invert__(self) -> Not:
 971        return not_(self.copy())
 972
 973
 974IntoType = t.Union[
 975    str,
 976    t.Type[Expression],
 977    t.Collection[t.Union[str, t.Type[Expression]]],
 978]
 979ExpOrStr = t.Union[str, Expression]
 980
 981
 982class Condition(Expression):
 983    """Logical conditions like x AND y, or simply x"""
 984
 985
 986class Predicate(Condition):
 987    """Relationships like x = y, x > 1, x >= y."""
 988
 989
 990class DerivedTable(Expression):
 991    @property
 992    def selects(self) -> t.List[Expression]:
 993        return self.this.selects if isinstance(self.this, Query) else []
 994
 995    @property
 996    def named_selects(self) -> t.List[str]:
 997        return [select.output_name for select in self.selects]
 998
 999
1000class Query(Expression):
1001    def subquery(self, alias: t.Optional[ExpOrStr] = None, copy: bool = True) -> Subquery:
1002        """
1003        Returns a `Subquery` that wraps around this query.
1004
1005        Example:
1006            >>> subquery = Select().select("x").from_("tbl").subquery()
1007            >>> Select().select("x").from_(subquery).sql()
1008            'SELECT x FROM (SELECT x FROM tbl)'
1009
1010        Args:
1011            alias: an optional alias for the subquery.
1012            copy: if `False`, modify this expression instance in-place.
1013        """
1014        instance = maybe_copy(self, copy)
1015        if not isinstance(alias, Expression):
1016            alias = TableAlias(this=to_identifier(alias)) if alias else None
1017
1018        return Subquery(this=instance, alias=alias)
1019
1020    def limit(
1021        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
1022    ) -> Select:
1023        """
1024        Adds a LIMIT clause to this query.
1025
1026        Example:
1027            >>> select("1").union(select("1")).limit(1).sql()
1028            'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1'
1029
1030        Args:
1031            expression: the SQL code string to parse.
1032                This can also be an integer.
1033                If a `Limit` instance is passed, it will be used as-is.
1034                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
1035            dialect: the dialect used to parse the input expression.
1036            copy: if `False`, modify this expression instance in-place.
1037            opts: other options to use to parse the input expressions.
1038
1039        Returns:
1040            A limited Select expression.
1041        """
1042        return (
1043            select("*")
1044            .from_(self.subquery(alias="_l_0", copy=copy))
1045            .limit(expression, dialect=dialect, copy=False, **opts)
1046        )
1047
1048    @property
1049    def ctes(self) -> t.List[CTE]:
1050        """Returns a list of all the CTEs attached to this query."""
1051        with_ = self.args.get("with")
1052        return with_.expressions if with_ else []
1053
1054    @property
1055    def selects(self) -> t.List[Expression]:
1056        """Returns the query's projections."""
1057        raise NotImplementedError("Query objects must implement `selects`")
1058
1059    @property
1060    def named_selects(self) -> t.List[str]:
1061        """Returns the output names of the query's projections."""
1062        raise NotImplementedError("Query objects must implement `named_selects`")
1063
1064    def select(
1065        self: Q,
1066        *expressions: t.Optional[ExpOrStr],
1067        append: bool = True,
1068        dialect: DialectType = None,
1069        copy: bool = True,
1070        **opts,
1071    ) -> Q:
1072        """
1073        Append to or set the SELECT expressions.
1074
1075        Example:
1076            >>> Select().select("x", "y").sql()
1077            'SELECT x, y'
1078
1079        Args:
1080            *expressions: the SQL code strings to parse.
1081                If an `Expression` instance is passed, it will be used as-is.
1082            append: if `True`, add to any existing expressions.
1083                Otherwise, this resets the expressions.
1084            dialect: the dialect used to parse the input expressions.
1085            copy: if `False`, modify this expression instance in-place.
1086            opts: other options to use to parse the input expressions.
1087
1088        Returns:
1089            The modified Query expression.
1090        """
1091        raise NotImplementedError("Query objects must implement `select`")
1092
1093    def with_(
1094        self: Q,
1095        alias: ExpOrStr,
1096        as_: ExpOrStr,
1097        recursive: t.Optional[bool] = None,
1098        append: bool = True,
1099        dialect: DialectType = None,
1100        copy: bool = True,
1101        **opts,
1102    ) -> Q:
1103        """
1104        Append to or set the common table expressions.
1105
1106        Example:
1107            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
1108            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
1109
1110        Args:
1111            alias: the SQL code string to parse as the table name.
1112                If an `Expression` instance is passed, this is used as-is.
1113            as_: the SQL code string to parse as the table expression.
1114                If an `Expression` instance is passed, it will be used as-is.
1115            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1116            append: if `True`, add to any existing expressions.
1117                Otherwise, this resets the expressions.
1118            dialect: the dialect used to parse the input expression.
1119            copy: if `False`, modify this expression instance in-place.
1120            opts: other options to use to parse the input expressions.
1121
1122        Returns:
1123            The modified expression.
1124        """
1125        return _apply_cte_builder(
1126            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
1127        )
1128
1129    def union(
1130        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1131    ) -> Union:
1132        """
1133        Builds a UNION expression.
1134
1135        Example:
1136            >>> import sqlglot
1137            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
1138            'SELECT * FROM foo UNION SELECT * FROM bla'
1139
1140        Args:
1141            expression: the SQL code string.
1142                If an `Expression` instance is passed, it will be used as-is.
1143            distinct: set the DISTINCT flag if and only if this is true.
1144            dialect: the dialect used to parse the input expression.
1145            opts: other options to use to parse the input expressions.
1146
1147        Returns:
1148            The new Union expression.
1149        """
1150        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1151
1152    def intersect(
1153        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1154    ) -> Intersect:
1155        """
1156        Builds an INTERSECT expression.
1157
1158        Example:
1159            >>> import sqlglot
1160            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
1161            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
1162
1163        Args:
1164            expression: the SQL code string.
1165                If an `Expression` instance is passed, it will be used as-is.
1166            distinct: set the DISTINCT flag if and only if this is true.
1167            dialect: the dialect used to parse the input expression.
1168            opts: other options to use to parse the input expressions.
1169
1170        Returns:
1171            The new Intersect expression.
1172        """
1173        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1174
1175    def except_(
1176        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1177    ) -> Except:
1178        """
1179        Builds an EXCEPT expression.
1180
1181        Example:
1182            >>> import sqlglot
1183            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
1184            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
1185
1186        Args:
1187            expression: the SQL code string.
1188                If an `Expression` instance is passed, it will be used as-is.
1189            distinct: set the DISTINCT flag if and only if this is true.
1190            dialect: the dialect used to parse the input expression.
1191            opts: other options to use to parse the input expressions.
1192
1193        Returns:
1194            The new Except expression.
1195        """
1196        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1197
1198
1199class UDTF(DerivedTable):
1200    @property
1201    def selects(self) -> t.List[Expression]:
1202        alias = self.args.get("alias")
1203        return alias.columns if alias else []
1204
1205
1206class Cache(Expression):
1207    arg_types = {
1208        "this": True,
1209        "lazy": False,
1210        "options": False,
1211        "expression": False,
1212    }
1213
1214
1215class Uncache(Expression):
1216    arg_types = {"this": True, "exists": False}
1217
1218
1219class Refresh(Expression):
1220    pass
1221
1222
1223class DDL(Expression):
1224    @property
1225    def ctes(self) -> t.List[CTE]:
1226        """Returns a list of all the CTEs attached to this statement."""
1227        with_ = self.args.get("with")
1228        return with_.expressions if with_ else []
1229
1230    @property
1231    def selects(self) -> t.List[Expression]:
1232        """If this statement contains a query (e.g. a CTAS), this returns the query's projections."""
1233        return self.expression.selects if isinstance(self.expression, Query) else []
1234
1235    @property
1236    def named_selects(self) -> t.List[str]:
1237        """
1238        If this statement contains a query (e.g. a CTAS), this returns the output
1239        names of the query's projections.
1240        """
1241        return self.expression.named_selects if isinstance(self.expression, Query) else []
1242
1243
1244class DML(Expression):
1245    def returning(
1246        self,
1247        expression: ExpOrStr,
1248        dialect: DialectType = None,
1249        copy: bool = True,
1250        **opts,
1251    ) -> DML:
1252        """
1253        Set the RETURNING expression. Not supported by all dialects.
1254
1255        Example:
1256            >>> delete("tbl").returning("*", dialect="postgres").sql()
1257            'DELETE FROM tbl RETURNING *'
1258
1259        Args:
1260            expression: the SQL code strings to parse.
1261                If an `Expression` instance is passed, it will be used as-is.
1262            dialect: the dialect used to parse the input expressions.
1263            copy: if `False`, modify this expression instance in-place.
1264            opts: other options to use to parse the input expressions.
1265
1266        Returns:
1267            Delete: the modified expression.
1268        """
1269        return _apply_builder(
1270            expression=expression,
1271            instance=self,
1272            arg="returning",
1273            prefix="RETURNING",
1274            dialect=dialect,
1275            copy=copy,
1276            into=Returning,
1277            **opts,
1278        )
1279
1280
1281class Create(DDL):
1282    arg_types = {
1283        "with": False,
1284        "this": True,
1285        "kind": True,
1286        "expression": False,
1287        "exists": False,
1288        "properties": False,
1289        "replace": False,
1290        "unique": False,
1291        "indexes": False,
1292        "no_schema_binding": False,
1293        "begin": False,
1294        "end": False,
1295        "clone": False,
1296    }
1297
1298    @property
1299    def kind(self) -> t.Optional[str]:
1300        kind = self.args.get("kind")
1301        return kind and kind.upper()
1302
1303
1304class SequenceProperties(Expression):
1305    arg_types = {
1306        "increment": False,
1307        "minvalue": False,
1308        "maxvalue": False,
1309        "cache": False,
1310        "start": False,
1311        "owned": False,
1312        "options": False,
1313    }
1314
1315
1316class TruncateTable(Expression):
1317    arg_types = {
1318        "expressions": True,
1319        "is_database": False,
1320        "exists": False,
1321        "only": False,
1322        "cluster": False,
1323        "identity": False,
1324        "option": False,
1325        "partition": False,
1326    }
1327
1328
1329# https://docs.snowflake.com/en/sql-reference/sql/create-clone
1330# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_clone_statement
1331# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_copy
1332class Clone(Expression):
1333    arg_types = {"this": True, "shallow": False, "copy": False}
1334
1335
1336class Describe(Expression):
1337    arg_types = {"this": True, "style": False, "kind": False, "expressions": False}
1338
1339
1340class Kill(Expression):
1341    arg_types = {"this": True, "kind": False}
1342
1343
1344class Pragma(Expression):
1345    pass
1346
1347
1348class Set(Expression):
1349    arg_types = {"expressions": False, "unset": False, "tag": False}
1350
1351
1352class Heredoc(Expression):
1353    arg_types = {"this": True, "tag": False}
1354
1355
1356class SetItem(Expression):
1357    arg_types = {
1358        "this": False,
1359        "expressions": False,
1360        "kind": False,
1361        "collate": False,  # MySQL SET NAMES statement
1362        "global": False,
1363    }
1364
1365
1366class Show(Expression):
1367    arg_types = {
1368        "this": True,
1369        "history": False,
1370        "terse": False,
1371        "target": False,
1372        "offset": False,
1373        "starts_with": False,
1374        "limit": False,
1375        "from": False,
1376        "like": False,
1377        "where": False,
1378        "db": False,
1379        "scope": False,
1380        "scope_kind": False,
1381        "full": False,
1382        "mutex": False,
1383        "query": False,
1384        "channel": False,
1385        "global": False,
1386        "log": False,
1387        "position": False,
1388        "types": False,
1389    }
1390
1391
1392class UserDefinedFunction(Expression):
1393    arg_types = {"this": True, "expressions": False, "wrapped": False}
1394
1395
1396class CharacterSet(Expression):
1397    arg_types = {"this": True, "default": False}
1398
1399
1400class With(Expression):
1401    arg_types = {"expressions": True, "recursive": False}
1402
1403    @property
1404    def recursive(self) -> bool:
1405        return bool(self.args.get("recursive"))
1406
1407
1408class WithinGroup(Expression):
1409    arg_types = {"this": True, "expression": False}
1410
1411
1412# clickhouse supports scalar ctes
1413# https://clickhouse.com/docs/en/sql-reference/statements/select/with
1414class CTE(DerivedTable):
1415    arg_types = {
1416        "this": True,
1417        "alias": True,
1418        "scalar": False,
1419        "materialized": False,
1420    }
1421
1422
1423class TableAlias(Expression):
1424    arg_types = {"this": False, "columns": False}
1425
1426    @property
1427    def columns(self):
1428        return self.args.get("columns") or []
1429
1430
1431class BitString(Condition):
1432    pass
1433
1434
1435class HexString(Condition):
1436    pass
1437
1438
1439class ByteString(Condition):
1440    pass
1441
1442
1443class RawString(Condition):
1444    pass
1445
1446
1447class UnicodeString(Condition):
1448    arg_types = {"this": True, "escape": False}
1449
1450
1451class Column(Condition):
1452    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
1453
1454    @property
1455    def table(self) -> str:
1456        return self.text("table")
1457
1458    @property
1459    def db(self) -> str:
1460        return self.text("db")
1461
1462    @property
1463    def catalog(self) -> str:
1464        return self.text("catalog")
1465
1466    @property
1467    def output_name(self) -> str:
1468        return self.name
1469
1470    @property
1471    def parts(self) -> t.List[Identifier]:
1472        """Return the parts of a column in order catalog, db, table, name."""
1473        return [
1474            t.cast(Identifier, self.args[part])
1475            for part in ("catalog", "db", "table", "this")
1476            if self.args.get(part)
1477        ]
1478
1479    def to_dot(self) -> Dot | Identifier:
1480        """Converts the column into a dot expression."""
1481        parts = self.parts
1482        parent = self.parent
1483
1484        while parent:
1485            if isinstance(parent, Dot):
1486                parts.append(parent.expression)
1487            parent = parent.parent
1488
1489        return Dot.build(deepcopy(parts)) if len(parts) > 1 else parts[0]
1490
1491
1492class ColumnPosition(Expression):
1493    arg_types = {"this": False, "position": True}
1494
1495
1496class ColumnDef(Expression):
1497    arg_types = {
1498        "this": True,
1499        "kind": False,
1500        "constraints": False,
1501        "exists": False,
1502        "position": False,
1503    }
1504
1505    @property
1506    def constraints(self) -> t.List[ColumnConstraint]:
1507        return self.args.get("constraints") or []
1508
1509    @property
1510    def kind(self) -> t.Optional[DataType]:
1511        return self.args.get("kind")
1512
1513
1514class AlterColumn(Expression):
1515    arg_types = {
1516        "this": True,
1517        "dtype": False,
1518        "collate": False,
1519        "using": False,
1520        "default": False,
1521        "drop": False,
1522        "comment": False,
1523    }
1524
1525
1526class RenameColumn(Expression):
1527    arg_types = {"this": True, "to": True, "exists": False}
1528
1529
1530class RenameTable(Expression):
1531    pass
1532
1533
1534class SwapTable(Expression):
1535    pass
1536
1537
1538class Comment(Expression):
1539    arg_types = {"this": True, "kind": True, "expression": True, "exists": False}
1540
1541
1542class Comprehension(Expression):
1543    arg_types = {"this": True, "expression": True, "iterator": True, "condition": False}
1544
1545
1546# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1547class MergeTreeTTLAction(Expression):
1548    arg_types = {
1549        "this": True,
1550        "delete": False,
1551        "recompress": False,
1552        "to_disk": False,
1553        "to_volume": False,
1554    }
1555
1556
1557# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1558class MergeTreeTTL(Expression):
1559    arg_types = {
1560        "expressions": True,
1561        "where": False,
1562        "group": False,
1563        "aggregates": False,
1564    }
1565
1566
1567# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1568class IndexConstraintOption(Expression):
1569    arg_types = {
1570        "key_block_size": False,
1571        "using": False,
1572        "parser": False,
1573        "comment": False,
1574        "visible": False,
1575        "engine_attr": False,
1576        "secondary_engine_attr": False,
1577    }
1578
1579
1580class ColumnConstraint(Expression):
1581    arg_types = {"this": False, "kind": True}
1582
1583    @property
1584    def kind(self) -> ColumnConstraintKind:
1585        return self.args["kind"]
1586
1587
1588class ColumnConstraintKind(Expression):
1589    pass
1590
1591
1592class AutoIncrementColumnConstraint(ColumnConstraintKind):
1593    pass
1594
1595
1596class PeriodForSystemTimeConstraint(ColumnConstraintKind):
1597    arg_types = {"this": True, "expression": True}
1598
1599
1600class CaseSpecificColumnConstraint(ColumnConstraintKind):
1601    arg_types = {"not_": True}
1602
1603
1604class CharacterSetColumnConstraint(ColumnConstraintKind):
1605    arg_types = {"this": True}
1606
1607
1608class CheckColumnConstraint(ColumnConstraintKind):
1609    arg_types = {"this": True, "enforced": False}
1610
1611
1612class ClusteredColumnConstraint(ColumnConstraintKind):
1613    pass
1614
1615
1616class CollateColumnConstraint(ColumnConstraintKind):
1617    pass
1618
1619
1620class CommentColumnConstraint(ColumnConstraintKind):
1621    pass
1622
1623
1624class CompressColumnConstraint(ColumnConstraintKind):
1625    pass
1626
1627
1628class DateFormatColumnConstraint(ColumnConstraintKind):
1629    arg_types = {"this": True}
1630
1631
1632class DefaultColumnConstraint(ColumnConstraintKind):
1633    pass
1634
1635
1636class EncodeColumnConstraint(ColumnConstraintKind):
1637    pass
1638
1639
1640# https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-EXCLUDE
1641class ExcludeColumnConstraint(ColumnConstraintKind):
1642    pass
1643
1644
1645class WithOperator(Expression):
1646    arg_types = {"this": True, "op": True}
1647
1648
1649class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
1650    # this: True -> ALWAYS, this: False -> BY DEFAULT
1651    arg_types = {
1652        "this": False,
1653        "expression": False,
1654        "on_null": False,
1655        "start": False,
1656        "increment": False,
1657        "minvalue": False,
1658        "maxvalue": False,
1659        "cycle": False,
1660    }
1661
1662
1663class GeneratedAsRowColumnConstraint(ColumnConstraintKind):
1664    arg_types = {"start": False, "hidden": False}
1665
1666
1667# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1668# https://github.com/ClickHouse/ClickHouse/blob/master/src/Parsers/ParserCreateQuery.h#L646
1669class IndexColumnConstraint(ColumnConstraintKind):
1670    arg_types = {
1671        "this": False,
1672        "schema": False,
1673        "kind": False,
1674        "index_type": False,
1675        "options": False,
1676        "expression": False,  # Clickhouse
1677        "granularity": False,
1678    }
1679
1680
1681class InlineLengthColumnConstraint(ColumnConstraintKind):
1682    pass
1683
1684
1685class NonClusteredColumnConstraint(ColumnConstraintKind):
1686    pass
1687
1688
1689class NotForReplicationColumnConstraint(ColumnConstraintKind):
1690    arg_types = {}
1691
1692
1693class NotNullColumnConstraint(ColumnConstraintKind):
1694    arg_types = {"allow_null": False}
1695
1696
1697# https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
1698class OnUpdateColumnConstraint(ColumnConstraintKind):
1699    pass
1700
1701
1702# https://docs.snowflake.com/en/sql-reference/sql/create-external-table#optional-parameters
1703class TransformColumnConstraint(ColumnConstraintKind):
1704    pass
1705
1706
1707class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1708    arg_types = {"desc": False}
1709
1710
1711class TitleColumnConstraint(ColumnConstraintKind):
1712    pass
1713
1714
1715class UniqueColumnConstraint(ColumnConstraintKind):
1716    arg_types = {"this": False, "index_type": False, "on_conflict": False}
1717
1718
1719class UppercaseColumnConstraint(ColumnConstraintKind):
1720    arg_types: t.Dict[str, t.Any] = {}
1721
1722
1723class PathColumnConstraint(ColumnConstraintKind):
1724    pass
1725
1726
1727# computed column expression
1728# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16
1729class ComputedColumnConstraint(ColumnConstraintKind):
1730    arg_types = {"this": True, "persisted": False, "not_null": False}
1731
1732
1733class Constraint(Expression):
1734    arg_types = {"this": True, "expressions": True}
1735
1736
1737class Delete(DML):
1738    arg_types = {
1739        "with": False,
1740        "this": False,
1741        "using": False,
1742        "where": False,
1743        "returning": False,
1744        "limit": False,
1745        "tables": False,  # Multiple-Table Syntax (MySQL)
1746    }
1747
1748    def delete(
1749        self,
1750        table: ExpOrStr,
1751        dialect: DialectType = None,
1752        copy: bool = True,
1753        **opts,
1754    ) -> Delete:
1755        """
1756        Create a DELETE expression or replace the table on an existing DELETE expression.
1757
1758        Example:
1759            >>> delete("tbl").sql()
1760            'DELETE FROM tbl'
1761
1762        Args:
1763            table: the table from which to delete.
1764            dialect: the dialect used to parse the input expression.
1765            copy: if `False`, modify this expression instance in-place.
1766            opts: other options to use to parse the input expressions.
1767
1768        Returns:
1769            Delete: the modified expression.
1770        """
1771        return _apply_builder(
1772            expression=table,
1773            instance=self,
1774            arg="this",
1775            dialect=dialect,
1776            into=Table,
1777            copy=copy,
1778            **opts,
1779        )
1780
1781    def where(
1782        self,
1783        *expressions: t.Optional[ExpOrStr],
1784        append: bool = True,
1785        dialect: DialectType = None,
1786        copy: bool = True,
1787        **opts,
1788    ) -> Delete:
1789        """
1790        Append to or set the WHERE expressions.
1791
1792        Example:
1793            >>> delete("tbl").where("x = 'a' OR x < 'b'").sql()
1794            "DELETE FROM tbl WHERE x = 'a' OR x < 'b'"
1795
1796        Args:
1797            *expressions: the SQL code strings to parse.
1798                If an `Expression` instance is passed, it will be used as-is.
1799                Multiple expressions are combined with an AND operator.
1800            append: if `True`, AND the new expressions to any existing expression.
1801                Otherwise, this resets the expression.
1802            dialect: the dialect used to parse the input expressions.
1803            copy: if `False`, modify this expression instance in-place.
1804            opts: other options to use to parse the input expressions.
1805
1806        Returns:
1807            Delete: the modified expression.
1808        """
1809        return _apply_conjunction_builder(
1810            *expressions,
1811            instance=self,
1812            arg="where",
1813            append=append,
1814            into=Where,
1815            dialect=dialect,
1816            copy=copy,
1817            **opts,
1818        )
1819
1820
1821class Drop(Expression):
1822    arg_types = {
1823        "this": False,
1824        "kind": False,
1825        "expressions": False,
1826        "exists": False,
1827        "temporary": False,
1828        "materialized": False,
1829        "cascade": False,
1830        "constraints": False,
1831        "purge": False,
1832    }
1833
1834
1835class Filter(Expression):
1836    arg_types = {"this": True, "expression": True}
1837
1838
1839class Check(Expression):
1840    pass
1841
1842
1843# https://docs.snowflake.com/en/sql-reference/constructs/connect-by
1844class Connect(Expression):
1845    arg_types = {"start": False, "connect": True, "nocycle": False}
1846
1847
1848class Prior(Expression):
1849    pass
1850
1851
1852class Directory(Expression):
1853    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1854    arg_types = {"this": True, "local": False, "row_format": False}
1855
1856
1857class ForeignKey(Expression):
1858    arg_types = {
1859        "expressions": True,
1860        "reference": False,
1861        "delete": False,
1862        "update": False,
1863    }
1864
1865
1866class ColumnPrefix(Expression):
1867    arg_types = {"this": True, "expression": True}
1868
1869
1870class PrimaryKey(Expression):
1871    arg_types = {"expressions": True, "options": False}
1872
1873
1874# https://www.postgresql.org/docs/9.1/sql-selectinto.html
1875# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
1876class Into(Expression):
1877    arg_types = {"this": True, "temporary": False, "unlogged": False}
1878
1879
1880class From(Expression):
1881    @property
1882    def name(self) -> str:
1883        return self.this.name
1884
1885    @property
1886    def alias_or_name(self) -> str:
1887        return self.this.alias_or_name
1888
1889
1890class Having(Expression):
1891    pass
1892
1893
1894class Hint(Expression):
1895    arg_types = {"expressions": True}
1896
1897
1898class JoinHint(Expression):
1899    arg_types = {"this": True, "expressions": True}
1900
1901
1902class Identifier(Expression):
1903    arg_types = {"this": True, "quoted": False, "global": False, "temporary": False}
1904
1905    @property
1906    def quoted(self) -> bool:
1907        return bool(self.args.get("quoted"))
1908
1909    @property
1910    def hashable_args(self) -> t.Any:
1911        return (self.this, self.quoted)
1912
1913    @property
1914    def output_name(self) -> str:
1915        return self.name
1916
1917
1918# https://www.postgresql.org/docs/current/indexes-opclass.html
1919class Opclass(Expression):
1920    arg_types = {"this": True, "expression": True}
1921
1922
1923class Index(Expression):
1924    arg_types = {
1925        "this": False,
1926        "table": False,
1927        "unique": False,
1928        "primary": False,
1929        "amp": False,  # teradata
1930        "params": False,
1931    }
1932
1933
1934class IndexParameters(Expression):
1935    arg_types = {
1936        "using": False,
1937        "include": False,
1938        "columns": False,
1939        "with_storage": False,
1940        "partition_by": False,
1941        "tablespace": False,
1942        "where": False,
1943    }
1944
1945
1946class Insert(DDL, DML):
1947    arg_types = {
1948        "hint": False,
1949        "with": False,
1950        "is_function": False,
1951        "this": True,
1952        "expression": False,
1953        "conflict": False,
1954        "returning": False,
1955        "overwrite": False,
1956        "exists": False,
1957        "partition": False,
1958        "alternative": False,
1959        "where": False,
1960        "ignore": False,
1961        "by_name": False,
1962    }
1963
1964    def with_(
1965        self,
1966        alias: ExpOrStr,
1967        as_: ExpOrStr,
1968        recursive: t.Optional[bool] = None,
1969        append: bool = True,
1970        dialect: DialectType = None,
1971        copy: bool = True,
1972        **opts,
1973    ) -> Insert:
1974        """
1975        Append to or set the common table expressions.
1976
1977        Example:
1978            >>> insert("SELECT x FROM cte", "t").with_("cte", as_="SELECT * FROM tbl").sql()
1979            'WITH cte AS (SELECT * FROM tbl) INSERT INTO t SELECT x FROM cte'
1980
1981        Args:
1982            alias: the SQL code string to parse as the table name.
1983                If an `Expression` instance is passed, this is used as-is.
1984            as_: the SQL code string to parse as the table expression.
1985                If an `Expression` instance is passed, it will be used as-is.
1986            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1987            append: if `True`, add to any existing expressions.
1988                Otherwise, this resets the expressions.
1989            dialect: the dialect used to parse the input expression.
1990            copy: if `False`, modify this expression instance in-place.
1991            opts: other options to use to parse the input expressions.
1992
1993        Returns:
1994            The modified expression.
1995        """
1996        return _apply_cte_builder(
1997            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
1998        )
1999
2000
2001class OnConflict(Expression):
2002    arg_types = {
2003        "duplicate": False,
2004        "expressions": False,
2005        "action": False,
2006        "conflict_keys": False,
2007        "constraint": False,
2008    }
2009
2010
2011class Returning(Expression):
2012    arg_types = {"expressions": True, "into": False}
2013
2014
2015# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
2016class Introducer(Expression):
2017    arg_types = {"this": True, "expression": True}
2018
2019
2020# national char, like n'utf8'
2021class National(Expression):
2022    pass
2023
2024
2025class LoadData(Expression):
2026    arg_types = {
2027        "this": True,
2028        "local": False,
2029        "overwrite": False,
2030        "inpath": True,
2031        "partition": False,
2032        "input_format": False,
2033        "serde": False,
2034    }
2035
2036
2037class Partition(Expression):
2038    arg_types = {"expressions": True}
2039
2040
2041class PartitionRange(Expression):
2042    arg_types = {"this": True, "expression": True}
2043
2044
2045class Fetch(Expression):
2046    arg_types = {
2047        "direction": False,
2048        "count": False,
2049        "percent": False,
2050        "with_ties": False,
2051    }
2052
2053
2054class Group(Expression):
2055    arg_types = {
2056        "expressions": False,
2057        "grouping_sets": False,
2058        "cube": False,
2059        "rollup": False,
2060        "totals": False,
2061        "all": False,
2062    }
2063
2064
2065class Lambda(Expression):
2066    arg_types = {"this": True, "expressions": True}
2067
2068
2069class Limit(Expression):
2070    arg_types = {"this": False, "expression": True, "offset": False, "expressions": False}
2071
2072
2073class Literal(Condition):
2074    arg_types = {"this": True, "is_string": True}
2075
2076    @property
2077    def hashable_args(self) -> t.Any:
2078        return (self.this, self.args.get("is_string"))
2079
2080    @classmethod
2081    def number(cls, number) -> Literal:
2082        return cls(this=str(number), is_string=False)
2083
2084    @classmethod
2085    def string(cls, string) -> Literal:
2086        return cls(this=str(string), is_string=True)
2087
2088    @property
2089    def output_name(self) -> str:
2090        return self.name
2091
2092
2093class Join(Expression):
2094    arg_types = {
2095        "this": True,
2096        "on": False,
2097        "side": False,
2098        "kind": False,
2099        "using": False,
2100        "method": False,
2101        "global": False,
2102        "hint": False,
2103        "match_condition": False,  # Snowflake
2104    }
2105
2106    @property
2107    def method(self) -> str:
2108        return self.text("method").upper()
2109
2110    @property
2111    def kind(self) -> str:
2112        return self.text("kind").upper()
2113
2114    @property
2115    def side(self) -> str:
2116        return self.text("side").upper()
2117
2118    @property
2119    def hint(self) -> str:
2120        return self.text("hint").upper()
2121
2122    @property
2123    def alias_or_name(self) -> str:
2124        return self.this.alias_or_name
2125
2126    def on(
2127        self,
2128        *expressions: t.Optional[ExpOrStr],
2129        append: bool = True,
2130        dialect: DialectType = None,
2131        copy: bool = True,
2132        **opts,
2133    ) -> Join:
2134        """
2135        Append to or set the ON expressions.
2136
2137        Example:
2138            >>> import sqlglot
2139            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
2140            'JOIN x ON y = 1'
2141
2142        Args:
2143            *expressions: the SQL code strings to parse.
2144                If an `Expression` instance is passed, it will be used as-is.
2145                Multiple expressions are combined with an AND operator.
2146            append: if `True`, AND the new expressions to any existing expression.
2147                Otherwise, this resets the expression.
2148            dialect: the dialect used to parse the input expressions.
2149            copy: if `False`, modify this expression instance in-place.
2150            opts: other options to use to parse the input expressions.
2151
2152        Returns:
2153            The modified Join expression.
2154        """
2155        join = _apply_conjunction_builder(
2156            *expressions,
2157            instance=self,
2158            arg="on",
2159            append=append,
2160            dialect=dialect,
2161            copy=copy,
2162            **opts,
2163        )
2164
2165        if join.kind == "CROSS":
2166            join.set("kind", None)
2167
2168        return join
2169
2170    def using(
2171        self,
2172        *expressions: t.Optional[ExpOrStr],
2173        append: bool = True,
2174        dialect: DialectType = None,
2175        copy: bool = True,
2176        **opts,
2177    ) -> Join:
2178        """
2179        Append to or set the USING expressions.
2180
2181        Example:
2182            >>> import sqlglot
2183            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
2184            'JOIN x USING (foo, bla)'
2185
2186        Args:
2187            *expressions: the SQL code strings to parse.
2188                If an `Expression` instance is passed, it will be used as-is.
2189            append: if `True`, concatenate the new expressions to the existing "using" list.
2190                Otherwise, this resets the expression.
2191            dialect: the dialect used to parse the input expressions.
2192            copy: if `False`, modify this expression instance in-place.
2193            opts: other options to use to parse the input expressions.
2194
2195        Returns:
2196            The modified Join expression.
2197        """
2198        join = _apply_list_builder(
2199            *expressions,
2200            instance=self,
2201            arg="using",
2202            append=append,
2203            dialect=dialect,
2204            copy=copy,
2205            **opts,
2206        )
2207
2208        if join.kind == "CROSS":
2209            join.set("kind", None)
2210
2211        return join
2212
2213
2214class Lateral(UDTF):
2215    arg_types = {
2216        "this": True,
2217        "view": False,
2218        "outer": False,
2219        "alias": False,
2220        "cross_apply": False,  # True -> CROSS APPLY, False -> OUTER APPLY
2221    }
2222
2223
2224class MatchRecognize(Expression):
2225    arg_types = {
2226        "partition_by": False,
2227        "order": False,
2228        "measures": False,
2229        "rows": False,
2230        "after": False,
2231        "pattern": False,
2232        "define": False,
2233        "alias": False,
2234    }
2235
2236
2237# Clickhouse FROM FINAL modifier
2238# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
2239class Final(Expression):
2240    pass
2241
2242
2243class Offset(Expression):
2244    arg_types = {"this": False, "expression": True, "expressions": False}
2245
2246
2247class Order(Expression):
2248    arg_types = {
2249        "this": False,
2250        "expressions": True,
2251        "interpolate": False,
2252        "siblings": False,
2253    }
2254
2255
2256# https://clickhouse.com/docs/en/sql-reference/statements/select/order-by#order-by-expr-with-fill-modifier
2257class WithFill(Expression):
2258    arg_types = {"from": False, "to": False, "step": False}
2259
2260
2261# hive specific sorts
2262# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
2263class Cluster(Order):
2264    pass
2265
2266
2267class Distribute(Order):
2268    pass
2269
2270
2271class Sort(Order):
2272    pass
2273
2274
2275class Ordered(Expression):
2276    arg_types = {"this": True, "desc": False, "nulls_first": True, "with_fill": False}
2277
2278
2279class Property(Expression):
2280    arg_types = {"this": True, "value": True}
2281
2282
2283class AlgorithmProperty(Property):
2284    arg_types = {"this": True}
2285
2286
2287class AutoIncrementProperty(Property):
2288    arg_types = {"this": True}
2289
2290
2291# https://docs.aws.amazon.com/prescriptive-guidance/latest/materialized-views-redshift/refreshing-materialized-views.html
2292class AutoRefreshProperty(Property):
2293    arg_types = {"this": True}
2294
2295
2296class BackupProperty(Property):
2297    arg_types = {"this": True}
2298
2299
2300class BlockCompressionProperty(Property):
2301    arg_types = {
2302        "autotemp": False,
2303        "always": False,
2304        "default": False,
2305        "manual": False,
2306        "never": False,
2307    }
2308
2309
2310class CharacterSetProperty(Property):
2311    arg_types = {"this": True, "default": True}
2312
2313
2314class ChecksumProperty(Property):
2315    arg_types = {"on": False, "default": False}
2316
2317
2318class CollateProperty(Property):
2319    arg_types = {"this": True, "default": False}
2320
2321
2322class CopyGrantsProperty(Property):
2323    arg_types = {}
2324
2325
2326class DataBlocksizeProperty(Property):
2327    arg_types = {
2328        "size": False,
2329        "units": False,
2330        "minimum": False,
2331        "maximum": False,
2332        "default": False,
2333    }
2334
2335
2336class DefinerProperty(Property):
2337    arg_types = {"this": True}
2338
2339
2340class DistKeyProperty(Property):
2341    arg_types = {"this": True}
2342
2343
2344class DistStyleProperty(Property):
2345    arg_types = {"this": True}
2346
2347
2348class EngineProperty(Property):
2349    arg_types = {"this": True}
2350
2351
2352class HeapProperty(Property):
2353    arg_types = {}
2354
2355
2356class ToTableProperty(Property):
2357    arg_types = {"this": True}
2358
2359
2360class ExecuteAsProperty(Property):
2361    arg_types = {"this": True}
2362
2363
2364class ExternalProperty(Property):
2365    arg_types = {"this": False}
2366
2367
2368class FallbackProperty(Property):
2369    arg_types = {"no": True, "protection": False}
2370
2371
2372class FileFormatProperty(Property):
2373    arg_types = {"this": True}
2374
2375
2376class FreespaceProperty(Property):
2377    arg_types = {"this": True, "percent": False}
2378
2379
2380class GlobalProperty(Property):
2381    arg_types = {}
2382
2383
2384class IcebergProperty(Property):
2385    arg_types = {}
2386
2387
2388class InheritsProperty(Property):
2389    arg_types = {"expressions": True}
2390
2391
2392class InputModelProperty(Property):
2393    arg_types = {"this": True}
2394
2395
2396class OutputModelProperty(Property):
2397    arg_types = {"this": True}
2398
2399
2400class IsolatedLoadingProperty(Property):
2401    arg_types = {"no": False, "concurrent": False, "target": False}
2402
2403
2404class JournalProperty(Property):
2405    arg_types = {
2406        "no": False,
2407        "dual": False,
2408        "before": False,
2409        "local": False,
2410        "after": False,
2411    }
2412
2413
2414class LanguageProperty(Property):
2415    arg_types = {"this": True}
2416
2417
2418# spark ddl
2419class ClusteredByProperty(Property):
2420    arg_types = {"expressions": True, "sorted_by": False, "buckets": True}
2421
2422
2423class DictProperty(Property):
2424    arg_types = {"this": True, "kind": True, "settings": False}
2425
2426
2427class DictSubProperty(Property):
2428    pass
2429
2430
2431class DictRange(Property):
2432    arg_types = {"this": True, "min": True, "max": True}
2433
2434
2435# Clickhouse CREATE ... ON CLUSTER modifier
2436# https://clickhouse.com/docs/en/sql-reference/distributed-ddl
2437class OnCluster(Property):
2438    arg_types = {"this": True}
2439
2440
2441class LikeProperty(Property):
2442    arg_types = {"this": True, "expressions": False}
2443
2444
2445class LocationProperty(Property):
2446    arg_types = {"this": True}
2447
2448
2449class LockProperty(Property):
2450    arg_types = {"this": True}
2451
2452
2453class LockingProperty(Property):
2454    arg_types = {
2455        "this": False,
2456        "kind": True,
2457        "for_or_in": False,
2458        "lock_type": True,
2459        "override": False,
2460    }
2461
2462
2463class LogProperty(Property):
2464    arg_types = {"no": True}
2465
2466
2467class MaterializedProperty(Property):
2468    arg_types = {"this": False}
2469
2470
2471class MergeBlockRatioProperty(Property):
2472    arg_types = {"this": False, "no": False, "default": False, "percent": False}
2473
2474
2475class NoPrimaryIndexProperty(Property):
2476    arg_types = {}
2477
2478
2479class OnProperty(Property):
2480    arg_types = {"this": True}
2481
2482
2483class OnCommitProperty(Property):
2484    arg_types = {"delete": False}
2485
2486
2487class PartitionedByProperty(Property):
2488    arg_types = {"this": True}
2489
2490
2491# https://www.postgresql.org/docs/current/sql-createtable.html
2492class PartitionBoundSpec(Expression):
2493    # this -> IN / MODULUS, expression -> REMAINDER, from_expressions -> FROM (...), to_expressions -> TO (...)
2494    arg_types = {
2495        "this": False,
2496        "expression": False,
2497        "from_expressions": False,
2498        "to_expressions": False,
2499    }
2500
2501
2502class PartitionedOfProperty(Property):
2503    # this -> parent_table (schema), expression -> FOR VALUES ... / DEFAULT
2504    arg_types = {"this": True, "expression": True}
2505
2506
2507class RemoteWithConnectionModelProperty(Property):
2508    arg_types = {"this": True}
2509
2510
2511class ReturnsProperty(Property):
2512    arg_types = {"this": True, "is_table": False, "table": False}
2513
2514
2515class RowFormatProperty(Property):
2516    arg_types = {"this": True}
2517
2518
2519class RowFormatDelimitedProperty(Property):
2520    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
2521    arg_types = {
2522        "fields": False,
2523        "escaped": False,
2524        "collection_items": False,
2525        "map_keys": False,
2526        "lines": False,
2527        "null": False,
2528        "serde": False,
2529    }
2530
2531
2532class RowFormatSerdeProperty(Property):
2533    arg_types = {"this": True, "serde_properties": False}
2534
2535
2536# https://spark.apache.org/docs/3.1.2/sql-ref-syntax-qry-select-transform.html
2537class QueryTransform(Expression):
2538    arg_types = {
2539        "expressions": True,
2540        "command_script": True,
2541        "schema": False,
2542        "row_format_before": False,
2543        "record_writer": False,
2544        "row_format_after": False,
2545        "record_reader": False,
2546    }
2547
2548
2549class SampleProperty(Property):
2550    arg_types = {"this": True}
2551
2552
2553class SchemaCommentProperty(Property):
2554    arg_types = {"this": True}
2555
2556
2557class SerdeProperties(Property):
2558    arg_types = {"expressions": True}
2559
2560
2561class SetProperty(Property):
2562    arg_types = {"multi": True}
2563
2564
2565class SharingProperty(Property):
2566    arg_types = {"this": False}
2567
2568
2569class SetConfigProperty(Property):
2570    arg_types = {"this": True}
2571
2572
2573class SettingsProperty(Property):
2574    arg_types = {"expressions": True}
2575
2576
2577class SortKeyProperty(Property):
2578    arg_types = {"this": True, "compound": False}
2579
2580
2581class SqlReadWriteProperty(Property):
2582    arg_types = {"this": True}
2583
2584
2585class SqlSecurityProperty(Property):
2586    arg_types = {"definer": True}
2587
2588
2589class StabilityProperty(Property):
2590    arg_types = {"this": True}
2591
2592
2593class TemporaryProperty(Property):
2594    arg_types = {"this": False}
2595
2596
2597class TransformModelProperty(Property):
2598    arg_types = {"expressions": True}
2599
2600
2601class TransientProperty(Property):
2602    arg_types = {"this": False}
2603
2604
2605class UnloggedProperty(Property):
2606    arg_types = {}
2607
2608
2609# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-ver16
2610class ViewAttributeProperty(Property):
2611    arg_types = {"this": True}
2612
2613
2614class VolatileProperty(Property):
2615    arg_types = {"this": False}
2616
2617
2618class WithDataProperty(Property):
2619    arg_types = {"no": True, "statistics": False}
2620
2621
2622class WithJournalTableProperty(Property):
2623    arg_types = {"this": True}
2624
2625
2626class WithSystemVersioningProperty(Property):
2627    # this -> history table name, expression -> data consistency check
2628    arg_types = {"this": False, "expression": False}
2629
2630
2631class Properties(Expression):
2632    arg_types = {"expressions": True}
2633
2634    NAME_TO_PROPERTY = {
2635        "ALGORITHM": AlgorithmProperty,
2636        "AUTO_INCREMENT": AutoIncrementProperty,
2637        "CHARACTER SET": CharacterSetProperty,
2638        "CLUSTERED_BY": ClusteredByProperty,
2639        "COLLATE": CollateProperty,
2640        "COMMENT": SchemaCommentProperty,
2641        "DEFINER": DefinerProperty,
2642        "DISTKEY": DistKeyProperty,
2643        "DISTSTYLE": DistStyleProperty,
2644        "ENGINE": EngineProperty,
2645        "EXECUTE AS": ExecuteAsProperty,
2646        "FORMAT": FileFormatProperty,
2647        "LANGUAGE": LanguageProperty,
2648        "LOCATION": LocationProperty,
2649        "LOCK": LockProperty,
2650        "PARTITIONED_BY": PartitionedByProperty,
2651        "RETURNS": ReturnsProperty,
2652        "ROW_FORMAT": RowFormatProperty,
2653        "SORTKEY": SortKeyProperty,
2654    }
2655
2656    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
2657
2658    # CREATE property locations
2659    # Form: schema specified
2660    #   create [POST_CREATE]
2661    #     table a [POST_NAME]
2662    #     (b int) [POST_SCHEMA]
2663    #     with ([POST_WITH])
2664    #     index (b) [POST_INDEX]
2665    #
2666    # Form: alias selection
2667    #   create [POST_CREATE]
2668    #     table a [POST_NAME]
2669    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
2670    #     index (c) [POST_INDEX]
2671    class Location(AutoName):
2672        POST_CREATE = auto()
2673        POST_NAME = auto()
2674        POST_SCHEMA = auto()
2675        POST_WITH = auto()
2676        POST_ALIAS = auto()
2677        POST_EXPRESSION = auto()
2678        POST_INDEX = auto()
2679        UNSUPPORTED = auto()
2680
2681    @classmethod
2682    def from_dict(cls, properties_dict: t.Dict) -> Properties:
2683        expressions = []
2684        for key, value in properties_dict.items():
2685            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
2686            if property_cls:
2687                expressions.append(property_cls(this=convert(value)))
2688            else:
2689                expressions.append(Property(this=Literal.string(key), value=convert(value)))
2690
2691        return cls(expressions=expressions)
2692
2693
2694class Qualify(Expression):
2695    pass
2696
2697
2698class InputOutputFormat(Expression):
2699    arg_types = {"input_format": False, "output_format": False}
2700
2701
2702# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
2703class Return(Expression):
2704    pass
2705
2706
2707class Reference(Expression):
2708    arg_types = {"this": True, "expressions": False, "options": False}
2709
2710
2711class Tuple(Expression):
2712    arg_types = {"expressions": False}
2713
2714    def isin(
2715        self,
2716        *expressions: t.Any,
2717        query: t.Optional[ExpOrStr] = None,
2718        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
2719        copy: bool = True,
2720        **opts,
2721    ) -> In:
2722        return In(
2723            this=maybe_copy(self, copy),
2724            expressions=[convert(e, copy=copy) for e in expressions],
2725            query=maybe_parse(query, copy=copy, **opts) if query else None,
2726            unnest=(
2727                Unnest(
2728                    expressions=[
2729                        maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts)
2730                        for e in ensure_list(unnest)
2731                    ]
2732                )
2733                if unnest
2734                else None
2735            ),
2736        )
2737
2738
2739QUERY_MODIFIERS = {
2740    "match": False,
2741    "laterals": False,
2742    "joins": False,
2743    "connect": False,
2744    "pivots": False,
2745    "prewhere": False,
2746    "where": False,
2747    "group": False,
2748    "having": False,
2749    "qualify": False,
2750    "windows": False,
2751    "distribute": False,
2752    "sort": False,
2753    "cluster": False,
2754    "order": False,
2755    "limit": False,
2756    "offset": False,
2757    "locks": False,
2758    "sample": False,
2759    "settings": False,
2760    "format": False,
2761    "options": False,
2762}
2763
2764
2765# https://learn.microsoft.com/en-us/sql/t-sql/queries/option-clause-transact-sql?view=sql-server-ver16
2766# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver16
2767class QueryOption(Expression):
2768    arg_types = {"this": True, "expression": False}
2769
2770
2771# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver16
2772class WithTableHint(Expression):
2773    arg_types = {"expressions": True}
2774
2775
2776# https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
2777class IndexTableHint(Expression):
2778    arg_types = {"this": True, "expressions": False, "target": False}
2779
2780
2781# https://docs.snowflake.com/en/sql-reference/constructs/at-before
2782class HistoricalData(Expression):
2783    arg_types = {"this": True, "kind": True, "expression": True}
2784
2785
2786class Table(Expression):
2787    arg_types = {
2788        "this": False,
2789        "alias": False,
2790        "db": False,
2791        "catalog": False,
2792        "laterals": False,
2793        "joins": False,
2794        "pivots": False,
2795        "hints": False,
2796        "system_time": False,
2797        "version": False,
2798        "format": False,
2799        "pattern": False,
2800        "ordinality": False,
2801        "when": False,
2802        "only": False,
2803    }
2804
2805    @property
2806    def name(self) -> str:
2807        if isinstance(self.this, Func):
2808            return ""
2809        return self.this.name
2810
2811    @property
2812    def db(self) -> str:
2813        return self.text("db")
2814
2815    @property
2816    def catalog(self) -> str:
2817        return self.text("catalog")
2818
2819    @property
2820    def selects(self) -> t.List[Expression]:
2821        return []
2822
2823    @property
2824    def named_selects(self) -> t.List[str]:
2825        return []
2826
2827    @property
2828    def parts(self) -> t.List[Expression]:
2829        """Return the parts of a table in order catalog, db, table."""
2830        parts: t.List[Expression] = []
2831
2832        for arg in ("catalog", "db", "this"):
2833            part = self.args.get(arg)
2834
2835            if isinstance(part, Dot):
2836                parts.extend(part.flatten())
2837            elif isinstance(part, Expression):
2838                parts.append(part)
2839
2840        return parts
2841
2842    def to_column(self, copy: bool = True) -> Alias | Column | Dot:
2843        parts = self.parts
2844        col = column(*reversed(parts[0:4]), fields=parts[4:], copy=copy)  # type: ignore
2845        alias = self.args.get("alias")
2846        if alias:
2847            col = alias_(col, alias.this, copy=copy)
2848        return col
2849
2850
2851class Union(Query):
2852    arg_types = {
2853        "with": False,
2854        "this": True,
2855        "expression": True,
2856        "distinct": False,
2857        "by_name": False,
2858        **QUERY_MODIFIERS,
2859    }
2860
2861    def select(
2862        self,
2863        *expressions: t.Optional[ExpOrStr],
2864        append: bool = True,
2865        dialect: DialectType = None,
2866        copy: bool = True,
2867        **opts,
2868    ) -> Union:
2869        this = maybe_copy(self, copy)
2870        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
2871        this.expression.unnest().select(
2872            *expressions, append=append, dialect=dialect, copy=False, **opts
2873        )
2874        return this
2875
2876    @property
2877    def named_selects(self) -> t.List[str]:
2878        return self.this.unnest().named_selects
2879
2880    @property
2881    def is_star(self) -> bool:
2882        return self.this.is_star or self.expression.is_star
2883
2884    @property
2885    def selects(self) -> t.List[Expression]:
2886        return self.this.unnest().selects
2887
2888    @property
2889    def left(self) -> Expression:
2890        return self.this
2891
2892    @property
2893    def right(self) -> Expression:
2894        return self.expression
2895
2896
2897class Except(Union):
2898    pass
2899
2900
2901class Intersect(Union):
2902    pass
2903
2904
2905class Unnest(UDTF):
2906    arg_types = {
2907        "expressions": True,
2908        "alias": False,
2909        "offset": False,
2910    }
2911
2912    @property
2913    def selects(self) -> t.List[Expression]:
2914        columns = super().selects
2915        offset = self.args.get("offset")
2916        if offset:
2917            columns = columns + [to_identifier("offset") if offset is True else offset]
2918        return columns
2919
2920
2921class Update(Expression):
2922    arg_types = {
2923        "with": False,
2924        "this": False,
2925        "expressions": True,
2926        "from": False,
2927        "where": False,
2928        "returning": False,
2929        "order": False,
2930        "limit": False,
2931    }
2932
2933
2934class Values(UDTF):
2935    arg_types = {"expressions": True, "alias": False}
2936
2937
2938class Var(Expression):
2939    pass
2940
2941
2942class Version(Expression):
2943    """
2944    Time travel, iceberg, bigquery etc
2945    https://trino.io/docs/current/connector/iceberg.html?highlight=snapshot#using-snapshots
2946    https://www.databricks.com/blog/2019/02/04/introducing-delta-time-travel-for-large-scale-data-lakes.html
2947    https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#for_system_time_as_of
2948    https://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver16
2949    this is either TIMESTAMP or VERSION
2950    kind is ("AS OF", "BETWEEN")
2951    """
2952
2953    arg_types = {"this": True, "kind": True, "expression": False}
2954
2955
2956class Schema(Expression):
2957    arg_types = {"this": False, "expressions": False}
2958
2959
2960# https://dev.mysql.com/doc/refman/8.0/en/select.html
2961# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html
2962class Lock(Expression):
2963    arg_types = {"update": True, "expressions": False, "wait": False}
2964
2965
2966class Select(Query):
2967    arg_types = {
2968        "with": False,
2969        "kind": False,
2970        "expressions": False,
2971        "hint": False,
2972        "distinct": False,
2973        "into": False,
2974        "from": False,
2975        **QUERY_MODIFIERS,
2976    }
2977
2978    def from_(
2979        self, expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
2980    ) -> Select:
2981        """
2982        Set the FROM expression.
2983
2984        Example:
2985            >>> Select().from_("tbl").select("x").sql()
2986            'SELECT x FROM tbl'
2987
2988        Args:
2989            expression : the SQL code strings to parse.
2990                If a `From` instance is passed, this is used as-is.
2991                If another `Expression` instance is passed, it will be wrapped in a `From`.
2992            dialect: the dialect used to parse the input expression.
2993            copy: if `False`, modify this expression instance in-place.
2994            opts: other options to use to parse the input expressions.
2995
2996        Returns:
2997            The modified Select expression.
2998        """
2999        return _apply_builder(
3000            expression=expression,
3001            instance=self,
3002            arg="from",
3003            into=From,
3004            prefix="FROM",
3005            dialect=dialect,
3006            copy=copy,
3007            **opts,
3008        )
3009
3010    def group_by(
3011        self,
3012        *expressions: t.Optional[ExpOrStr],
3013        append: bool = True,
3014        dialect: DialectType = None,
3015        copy: bool = True,
3016        **opts,
3017    ) -> Select:
3018        """
3019        Set the GROUP BY expression.
3020
3021        Example:
3022            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
3023            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
3024
3025        Args:
3026            *expressions: the SQL code strings to parse.
3027                If a `Group` instance is passed, this is used as-is.
3028                If another `Expression` instance is passed, it will be wrapped in a `Group`.
3029                If nothing is passed in then a group by is not applied to the expression
3030            append: if `True`, add to any existing expressions.
3031                Otherwise, this flattens all the `Group` expression into a single expression.
3032            dialect: the dialect used to parse the input expression.
3033            copy: if `False`, modify this expression instance in-place.
3034            opts: other options to use to parse the input expressions.
3035
3036        Returns:
3037            The modified Select expression.
3038        """
3039        if not expressions:
3040            return self if not copy else self.copy()
3041
3042        return _apply_child_list_builder(
3043            *expressions,
3044            instance=self,
3045            arg="group",
3046            append=append,
3047            copy=copy,
3048            prefix="GROUP BY",
3049            into=Group,
3050            dialect=dialect,
3051            **opts,
3052        )
3053
3054    def order_by(
3055        self,
3056        *expressions: t.Optional[ExpOrStr],
3057        append: bool = True,
3058        dialect: DialectType = None,
3059        copy: bool = True,
3060        **opts,
3061    ) -> Select:
3062        """
3063        Set the ORDER BY expression.
3064
3065        Example:
3066            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
3067            'SELECT x FROM tbl ORDER BY x DESC'
3068
3069        Args:
3070            *expressions: the SQL code strings to parse.
3071                If a `Group` instance is passed, this is used as-is.
3072                If another `Expression` instance is passed, it will be wrapped in a `Order`.
3073            append: if `True`, add to any existing expressions.
3074                Otherwise, this flattens all the `Order` expression into a single expression.
3075            dialect: the dialect used to parse the input expression.
3076            copy: if `False`, modify this expression instance in-place.
3077            opts: other options to use to parse the input expressions.
3078
3079        Returns:
3080            The modified Select expression.
3081        """
3082        return _apply_child_list_builder(
3083            *expressions,
3084            instance=self,
3085            arg="order",
3086            append=append,
3087            copy=copy,
3088            prefix="ORDER BY",
3089            into=Order,
3090            dialect=dialect,
3091            **opts,
3092        )
3093
3094    def sort_by(
3095        self,
3096        *expressions: t.Optional[ExpOrStr],
3097        append: bool = True,
3098        dialect: DialectType = None,
3099        copy: bool = True,
3100        **opts,
3101    ) -> Select:
3102        """
3103        Set the SORT BY expression.
3104
3105        Example:
3106            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql(dialect="hive")
3107            'SELECT x FROM tbl SORT BY x DESC'
3108
3109        Args:
3110            *expressions: the SQL code strings to parse.
3111                If a `Group` instance is passed, this is used as-is.
3112                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
3113            append: if `True`, add to any existing expressions.
3114                Otherwise, this flattens all the `Order` expression into a single expression.
3115            dialect: the dialect used to parse the input expression.
3116            copy: if `False`, modify this expression instance in-place.
3117            opts: other options to use to parse the input expressions.
3118
3119        Returns:
3120            The modified Select expression.
3121        """
3122        return _apply_child_list_builder(
3123            *expressions,
3124            instance=self,
3125            arg="sort",
3126            append=append,
3127            copy=copy,
3128            prefix="SORT BY",
3129            into=Sort,
3130            dialect=dialect,
3131            **opts,
3132        )
3133
3134    def cluster_by(
3135        self,
3136        *expressions: t.Optional[ExpOrStr],
3137        append: bool = True,
3138        dialect: DialectType = None,
3139        copy: bool = True,
3140        **opts,
3141    ) -> Select:
3142        """
3143        Set the CLUSTER BY expression.
3144
3145        Example:
3146            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql(dialect="hive")
3147            'SELECT x FROM tbl CLUSTER BY x DESC'
3148
3149        Args:
3150            *expressions: the SQL code strings to parse.
3151                If a `Group` instance is passed, this is used as-is.
3152                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
3153            append: if `True`, add to any existing expressions.
3154                Otherwise, this flattens all the `Order` expression into a single expression.
3155            dialect: the dialect used to parse the input expression.
3156            copy: if `False`, modify this expression instance in-place.
3157            opts: other options to use to parse the input expressions.
3158
3159        Returns:
3160            The modified Select expression.
3161        """
3162        return _apply_child_list_builder(
3163            *expressions,
3164            instance=self,
3165            arg="cluster",
3166            append=append,
3167            copy=copy,
3168            prefix="CLUSTER BY",
3169            into=Cluster,
3170            dialect=dialect,
3171            **opts,
3172        )
3173
3174    def limit(
3175        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
3176    ) -> Select:
3177        return _apply_builder(
3178            expression=expression,
3179            instance=self,
3180            arg="limit",
3181            into=Limit,
3182            prefix="LIMIT",
3183            dialect=dialect,
3184            copy=copy,
3185            into_arg="expression",
3186            **opts,
3187        )
3188
3189    def offset(
3190        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
3191    ) -> Select:
3192        """
3193        Set the OFFSET expression.
3194
3195        Example:
3196            >>> Select().from_("tbl").select("x").offset(10).sql()
3197            'SELECT x FROM tbl OFFSET 10'
3198
3199        Args:
3200            expression: the SQL code string to parse.
3201                This can also be an integer.
3202                If a `Offset` instance is passed, this is used as-is.
3203                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
3204            dialect: the dialect used to parse the input expression.
3205            copy: if `False`, modify this expression instance in-place.
3206            opts: other options to use to parse the input expressions.
3207
3208        Returns:
3209            The modified Select expression.
3210        """
3211        return _apply_builder(
3212            expression=expression,
3213            instance=self,
3214            arg="offset",
3215            into=Offset,
3216            prefix="OFFSET",
3217            dialect=dialect,
3218            copy=copy,
3219            into_arg="expression",
3220            **opts,
3221        )
3222
3223    def select(
3224        self,
3225        *expressions: t.Optional[ExpOrStr],
3226        append: bool = True,
3227        dialect: DialectType = None,
3228        copy: bool = True,
3229        **opts,
3230    ) -> Select:
3231        return _apply_list_builder(
3232            *expressions,
3233            instance=self,
3234            arg="expressions",
3235            append=append,
3236            dialect=dialect,
3237            into=Expression,
3238            copy=copy,
3239            **opts,
3240        )
3241
3242    def lateral(
3243        self,
3244        *expressions: t.Optional[ExpOrStr],
3245        append: bool = True,
3246        dialect: DialectType = None,
3247        copy: bool = True,
3248        **opts,
3249    ) -> Select:
3250        """
3251        Append to or set the LATERAL expressions.
3252
3253        Example:
3254            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
3255            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
3256
3257        Args:
3258            *expressions: the SQL code strings to parse.
3259                If an `Expression` instance is passed, it will be used as-is.
3260            append: if `True`, add to any existing expressions.
3261                Otherwise, this resets the expressions.
3262            dialect: the dialect used to parse the input expressions.
3263            copy: if `False`, modify this expression instance in-place.
3264            opts: other options to use to parse the input expressions.
3265
3266        Returns:
3267            The modified Select expression.
3268        """
3269        return _apply_list_builder(
3270            *expressions,
3271            instance=self,
3272            arg="laterals",
3273            append=append,
3274            into=Lateral,
3275            prefix="LATERAL VIEW",
3276            dialect=dialect,
3277            copy=copy,
3278            **opts,
3279        )
3280
3281    def join(
3282        self,
3283        expression: ExpOrStr,
3284        on: t.Optional[ExpOrStr] = None,
3285        using: t.Optional[ExpOrStr | t.Collection[ExpOrStr]] = None,
3286        append: bool = True,
3287        join_type: t.Optional[str] = None,
3288        join_alias: t.Optional[Identifier | str] = None,
3289        dialect: DialectType = None,
3290        copy: bool = True,
3291        **opts,
3292    ) -> Select:
3293        """
3294        Append to or set the JOIN expressions.
3295
3296        Example:
3297            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
3298            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
3299
3300            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
3301            'SELECT 1 FROM a JOIN b USING (x, y, z)'
3302
3303            Use `join_type` to change the type of join:
3304
3305            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
3306            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
3307
3308        Args:
3309            expression: the SQL code string to parse.
3310                If an `Expression` instance is passed, it will be used as-is.
3311            on: optionally specify the join "on" criteria as a SQL string.
3312                If an `Expression` instance is passed, it will be used as-is.
3313            using: optionally specify the join "using" criteria as a SQL string.
3314                If an `Expression` instance is passed, it will be used as-is.
3315            append: if `True`, add to any existing expressions.
3316                Otherwise, this resets the expressions.
3317            join_type: if set, alter the parsed join type.
3318            join_alias: an optional alias for the joined source.
3319            dialect: the dialect used to parse the input expressions.
3320            copy: if `False`, modify this expression instance in-place.
3321            opts: other options to use to parse the input expressions.
3322
3323        Returns:
3324            Select: the modified expression.
3325        """
3326        parse_args: t.Dict[str, t.Any] = {"dialect": dialect, **opts}
3327
3328        try:
3329            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
3330        except ParseError:
3331            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
3332
3333        join = expression if isinstance(expression, Join) else Join(this=expression)
3334
3335        if isinstance(join.this, Select):
3336            join.this.replace(join.this.subquery())
3337
3338        if join_type:
3339            method: t.Optional[Token]
3340            side: t.Optional[Token]
3341            kind: t.Optional[Token]
3342
3343            method, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
3344
3345            if method:
3346                join.set("method", method.text)
3347            if side:
3348                join.set("side", side.text)
3349            if kind:
3350                join.set("kind", kind.text)
3351
3352        if on:
3353            on = and_(*ensure_list(on), dialect=dialect, copy=copy, **opts)
3354            join.set("on", on)
3355
3356        if using:
3357            join = _apply_list_builder(
3358                *ensure_list(using),
3359                instance=join,
3360                arg="using",
3361                append=append,
3362                copy=copy,
3363                into=Identifier,
3364                **opts,
3365            )
3366
3367        if join_alias:
3368            join.set("this", alias_(join.this, join_alias, table=True))
3369
3370        return _apply_list_builder(
3371            join,
3372            instance=self,
3373            arg="joins",
3374            append=append,
3375            copy=copy,
3376            **opts,
3377        )
3378
3379    def where(
3380        self,
3381        *expressions: t.Optional[ExpOrStr],
3382        append: bool = True,
3383        dialect: DialectType = None,
3384        copy: bool = True,
3385        **opts,
3386    ) -> Select:
3387        """
3388        Append to or set the WHERE expressions.
3389
3390        Example:
3391            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
3392            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
3393
3394        Args:
3395            *expressions: the SQL code strings to parse.
3396                If an `Expression` instance is passed, it will be used as-is.
3397                Multiple expressions are combined with an AND operator.
3398            append: if `True`, AND the new expressions to any existing expression.
3399                Otherwise, this resets the expression.
3400            dialect: the dialect used to parse the input expressions.
3401            copy: if `False`, modify this expression instance in-place.
3402            opts: other options to use to parse the input expressions.
3403
3404        Returns:
3405            Select: the modified expression.
3406        """
3407        return _apply_conjunction_builder(
3408            *expressions,
3409            instance=self,
3410            arg="where",
3411            append=append,
3412            into=Where,
3413            dialect=dialect,
3414            copy=copy,
3415            **opts,
3416        )
3417
3418    def having(
3419        self,
3420        *expressions: t.Optional[ExpOrStr],
3421        append: bool = True,
3422        dialect: DialectType = None,
3423        copy: bool = True,
3424        **opts,
3425    ) -> Select:
3426        """
3427        Append to or set the HAVING expressions.
3428
3429        Example:
3430            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
3431            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
3432
3433        Args:
3434            *expressions: the SQL code strings to parse.
3435                If an `Expression` instance is passed, it will be used as-is.
3436                Multiple expressions are combined with an AND operator.
3437            append: if `True`, AND the new expressions to any existing expression.
3438                Otherwise, this resets the expression.
3439            dialect: the dialect used to parse the input expressions.
3440            copy: if `False`, modify this expression instance in-place.
3441            opts: other options to use to parse the input expressions.
3442
3443        Returns:
3444            The modified Select expression.
3445        """
3446        return _apply_conjunction_builder(
3447            *expressions,
3448            instance=self,
3449            arg="having",
3450            append=append,
3451            into=Having,
3452            dialect=dialect,
3453            copy=copy,
3454            **opts,
3455        )
3456
3457    def window(
3458        self,
3459        *expressions: t.Optional[ExpOrStr],
3460        append: bool = True,
3461        dialect: DialectType = None,
3462        copy: bool = True,
3463        **opts,
3464    ) -> Select:
3465        return _apply_list_builder(
3466            *expressions,
3467            instance=self,
3468            arg="windows",
3469            append=append,
3470            into=Window,
3471            dialect=dialect,
3472            copy=copy,
3473            **opts,
3474        )
3475
3476    def qualify(
3477        self,
3478        *expressions: t.Optional[ExpOrStr],
3479        append: bool = True,
3480        dialect: DialectType = None,
3481        copy: bool = True,
3482        **opts,
3483    ) -> Select:
3484        return _apply_conjunction_builder(
3485            *expressions,
3486            instance=self,
3487            arg="qualify",
3488            append=append,
3489            into=Qualify,
3490            dialect=dialect,
3491            copy=copy,
3492            **opts,
3493        )
3494
3495    def distinct(
3496        self, *ons: t.Optional[ExpOrStr], distinct: bool = True, copy: bool = True
3497    ) -> Select:
3498        """
3499        Set the OFFSET expression.
3500
3501        Example:
3502            >>> Select().from_("tbl").select("x").distinct().sql()
3503            'SELECT DISTINCT x FROM tbl'
3504
3505        Args:
3506            ons: the expressions to distinct on
3507            distinct: whether the Select should be distinct
3508            copy: if `False`, modify this expression instance in-place.
3509
3510        Returns:
3511            Select: the modified expression.
3512        """
3513        instance = maybe_copy(self, copy)
3514        on = Tuple(expressions=[maybe_parse(on, copy=copy) for on in ons if on]) if ons else None
3515        instance.set("distinct", Distinct(on=on) if distinct else None)
3516        return instance
3517
3518    def ctas(
3519        self,
3520        table: ExpOrStr,
3521        properties: t.Optional[t.Dict] = None,
3522        dialect: DialectType = None,
3523        copy: bool = True,
3524        **opts,
3525    ) -> Create:
3526        """
3527        Convert this expression to a CREATE TABLE AS statement.
3528
3529        Example:
3530            >>> Select().select("*").from_("tbl").ctas("x").sql()
3531            'CREATE TABLE x AS SELECT * FROM tbl'
3532
3533        Args:
3534            table: the SQL code string to parse as the table name.
3535                If another `Expression` instance is passed, it will be used as-is.
3536            properties: an optional mapping of table properties
3537            dialect: the dialect used to parse the input table.
3538            copy: if `False`, modify this expression instance in-place.
3539            opts: other options to use to parse the input table.
3540
3541        Returns:
3542            The new Create expression.
3543        """
3544        instance = maybe_copy(self, copy)
3545        table_expression = maybe_parse(table, into=Table, dialect=dialect, **opts)
3546
3547        properties_expression = None
3548        if properties:
3549            properties_expression = Properties.from_dict(properties)
3550
3551        return Create(
3552            this=table_expression,
3553            kind="TABLE",
3554            expression=instance,
3555            properties=properties_expression,
3556        )
3557
3558    def lock(self, update: bool = True, copy: bool = True) -> Select:
3559        """
3560        Set the locking read mode for this expression.
3561
3562        Examples:
3563            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
3564            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
3565
3566            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
3567            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
3568
3569        Args:
3570            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
3571            copy: if `False`, modify this expression instance in-place.
3572
3573        Returns:
3574            The modified expression.
3575        """
3576        inst = maybe_copy(self, copy)
3577        inst.set("locks", [Lock(update=update)])
3578
3579        return inst
3580
3581    def hint(self, *hints: ExpOrStr, dialect: DialectType = None, copy: bool = True) -> Select:
3582        """
3583        Set hints for this expression.
3584
3585        Examples:
3586            >>> Select().select("x").from_("tbl").hint("BROADCAST(y)").sql(dialect="spark")
3587            'SELECT /*+ BROADCAST(y) */ x FROM tbl'
3588
3589        Args:
3590            hints: The SQL code strings to parse as the hints.
3591                If an `Expression` instance is passed, it will be used as-is.
3592            dialect: The dialect used to parse the hints.
3593            copy: If `False`, modify this expression instance in-place.
3594
3595        Returns:
3596            The modified expression.
3597        """
3598        inst = maybe_copy(self, copy)
3599        inst.set(
3600            "hint", Hint(expressions=[maybe_parse(h, copy=copy, dialect=dialect) for h in hints])
3601        )
3602
3603        return inst
3604
3605    @property
3606    def named_selects(self) -> t.List[str]:
3607        return [e.output_name for e in self.expressions if e.alias_or_name]
3608
3609    @property
3610    def is_star(self) -> bool:
3611        return any(expression.is_star for expression in self.expressions)
3612
3613    @property
3614    def selects(self) -> t.List[Expression]:
3615        return self.expressions
3616
3617
3618UNWRAPPED_QUERIES = (Select, Union)
3619
3620
3621class Subquery(DerivedTable, Query):
3622    arg_types = {
3623        "this": True,
3624        "alias": False,
3625        "with": False,
3626        **QUERY_MODIFIERS,
3627    }
3628
3629    def unnest(self):
3630        """Returns the first non subquery."""
3631        expression = self
3632        while isinstance(expression, Subquery):
3633            expression = expression.this
3634        return expression
3635
3636    def unwrap(self) -> Subquery:
3637        expression = self
3638        while expression.same_parent and expression.is_wrapper:
3639            expression = t.cast(Subquery, expression.parent)
3640        return expression
3641
3642    def select(
3643        self,
3644        *expressions: t.Optional[ExpOrStr],
3645        append: bool = True,
3646        dialect: DialectType = None,
3647        copy: bool = True,
3648        **opts,
3649    ) -> Subquery:
3650        this = maybe_copy(self, copy)
3651        this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
3652        return this
3653
3654    @property
3655    def is_wrapper(self) -> bool:
3656        """
3657        Whether this Subquery acts as a simple wrapper around another expression.
3658
3659        SELECT * FROM (((SELECT * FROM t)))
3660                      ^
3661                      This corresponds to a "wrapper" Subquery node
3662        """
3663        return all(v is None for k, v in self.args.items() if k != "this")
3664
3665    @property
3666    def is_star(self) -> bool:
3667        return self.this.is_star
3668
3669    @property
3670    def output_name(self) -> str:
3671        return self.alias
3672
3673
3674class TableSample(Expression):
3675    arg_types = {
3676        "this": False,
3677        "expressions": False,
3678        "method": False,
3679        "bucket_numerator": False,
3680        "bucket_denominator": False,
3681        "bucket_field": False,
3682        "percent": False,
3683        "rows": False,
3684        "size": False,
3685        "seed": False,
3686    }
3687
3688
3689class Tag(Expression):
3690    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
3691
3692    arg_types = {
3693        "this": False,
3694        "prefix": False,
3695        "postfix": False,
3696    }
3697
3698
3699# Represents both the standard SQL PIVOT operator and DuckDB's "simplified" PIVOT syntax
3700# https://duckdb.org/docs/sql/statements/pivot
3701class Pivot(Expression):
3702    arg_types = {
3703        "this": False,
3704        "alias": False,
3705        "expressions": False,
3706        "field": False,
3707        "unpivot": False,
3708        "using": False,
3709        "group": False,
3710        "columns": False,
3711        "include_nulls": False,
3712    }
3713
3714    @property
3715    def unpivot(self) -> bool:
3716        return bool(self.args.get("unpivot"))
3717
3718
3719class Window(Condition):
3720    arg_types = {
3721        "this": True,
3722        "partition_by": False,
3723        "order": False,
3724        "spec": False,
3725        "alias": False,
3726        "over": False,
3727        "first": False,
3728    }
3729
3730
3731class WindowSpec(Expression):
3732    arg_types = {
3733        "kind": False,
3734        "start": False,
3735        "start_side": False,
3736        "end": False,
3737        "end_side": False,
3738    }
3739
3740
3741class PreWhere(Expression):
3742    pass
3743
3744
3745class Where(Expression):
3746    pass
3747
3748
3749class Star(Expression):
3750    arg_types = {"except": False, "replace": False}
3751
3752    @property
3753    def name(self) -> str:
3754        return "*"
3755
3756    @property
3757    def output_name(self) -> str:
3758        return self.name
3759
3760
3761class Parameter(Condition):
3762    arg_types = {"this": True, "expression": False}
3763
3764
3765class SessionParameter(Condition):
3766    arg_types = {"this": True, "kind": False}
3767
3768
3769class Placeholder(Condition):
3770    arg_types = {"this": False, "kind": False}
3771
3772
3773class Null(Condition):
3774    arg_types: t.Dict[str, t.Any] = {}
3775
3776    @property
3777    def name(self) -> str:
3778        return "NULL"
3779
3780
3781class Boolean(Condition):
3782    pass
3783
3784
3785class DataTypeParam(Expression):
3786    arg_types = {"this": True, "expression": False}
3787
3788    @property
3789    def name(self) -> str:
3790        return self.this.name
3791
3792
3793class DataType(Expression):
3794    arg_types = {
3795        "this": True,
3796        "expressions": False,
3797        "nested": False,
3798        "values": False,
3799        "prefix": False,
3800        "kind": False,
3801    }
3802
3803    class Type(AutoName):
3804        ARRAY = auto()
3805        AGGREGATEFUNCTION = auto()
3806        SIMPLEAGGREGATEFUNCTION = auto()
3807        BIGDECIMAL = auto()
3808        BIGINT = auto()
3809        BIGSERIAL = auto()
3810        BINARY = auto()
3811        BIT = auto()
3812        BOOLEAN = auto()
3813        BPCHAR = auto()
3814        CHAR = auto()
3815        DATE = auto()
3816        DATE32 = auto()
3817        DATEMULTIRANGE = auto()
3818        DATERANGE = auto()
3819        DATETIME = auto()
3820        DATETIME64 = auto()
3821        DECIMAL = auto()
3822        DOUBLE = auto()
3823        ENUM = auto()
3824        ENUM8 = auto()
3825        ENUM16 = auto()
3826        FIXEDSTRING = auto()
3827        FLOAT = auto()
3828        GEOGRAPHY = auto()
3829        GEOMETRY = auto()
3830        HLLSKETCH = auto()
3831        HSTORE = auto()
3832        IMAGE = auto()
3833        INET = auto()
3834        INT = auto()
3835        INT128 = auto()
3836        INT256 = auto()
3837        INT4MULTIRANGE = auto()
3838        INT4RANGE = auto()
3839        INT8MULTIRANGE = auto()
3840        INT8RANGE = auto()
3841        INTERVAL = auto()
3842        IPADDRESS = auto()
3843        IPPREFIX = auto()
3844        IPV4 = auto()
3845        IPV6 = auto()
3846        JSON = auto()
3847        JSONB = auto()
3848        LONGBLOB = auto()
3849        LONGTEXT = auto()
3850        LOWCARDINALITY = auto()
3851        MAP = auto()
3852        MEDIUMBLOB = auto()
3853        MEDIUMINT = auto()
3854        MEDIUMTEXT = auto()
3855        MONEY = auto()
3856        NAME = auto()
3857        NCHAR = auto()
3858        NESTED = auto()
3859        NULL = auto()
3860        NULLABLE = auto()
3861        NUMMULTIRANGE = auto()
3862        NUMRANGE = auto()
3863        NVARCHAR = auto()
3864        OBJECT = auto()
3865        ROWVERSION = auto()
3866        SERIAL = auto()
3867        SET = auto()
3868        SMALLINT = auto()
3869        SMALLMONEY = auto()
3870        SMALLSERIAL = auto()
3871        STRUCT = auto()
3872        SUPER = auto()
3873        TEXT = auto()
3874        TINYBLOB = auto()
3875        TINYTEXT = auto()
3876        TIME = auto()
3877        TIMETZ = auto()
3878        TIMESTAMP = auto()
3879        TIMESTAMPLTZ = auto()
3880        TIMESTAMPTZ = auto()
3881        TIMESTAMP_S = auto()
3882        TIMESTAMP_MS = auto()
3883        TIMESTAMP_NS = auto()
3884        TINYINT = auto()
3885        TSMULTIRANGE = auto()
3886        TSRANGE = auto()
3887        TSTZMULTIRANGE = auto()
3888        TSTZRANGE = auto()
3889        UBIGINT = auto()
3890        UINT = auto()
3891        UINT128 = auto()
3892        UINT256 = auto()
3893        UMEDIUMINT = auto()
3894        UDECIMAL = auto()
3895        UNIQUEIDENTIFIER = auto()
3896        UNKNOWN = auto()  # Sentinel value, useful for type annotation
3897        USERDEFINED = "USER-DEFINED"
3898        USMALLINT = auto()
3899        UTINYINT = auto()
3900        UUID = auto()
3901        VARBINARY = auto()
3902        VARCHAR = auto()
3903        VARIANT = auto()
3904        XML = auto()
3905        YEAR = auto()
3906
3907    STRUCT_TYPES = {
3908        Type.NESTED,
3909        Type.OBJECT,
3910        Type.STRUCT,
3911    }
3912
3913    NESTED_TYPES = {
3914        *STRUCT_TYPES,
3915        Type.ARRAY,
3916        Type.MAP,
3917    }
3918
3919    TEXT_TYPES = {
3920        Type.CHAR,
3921        Type.NCHAR,
3922        Type.NVARCHAR,
3923        Type.TEXT,
3924        Type.VARCHAR,
3925        Type.NAME,
3926    }
3927
3928    SIGNED_INTEGER_TYPES = {
3929        Type.BIGINT,
3930        Type.INT,
3931        Type.INT128,
3932        Type.INT256,
3933        Type.MEDIUMINT,
3934        Type.SMALLINT,
3935        Type.TINYINT,
3936    }
3937
3938    UNSIGNED_INTEGER_TYPES = {
3939        Type.UBIGINT,
3940        Type.UINT,
3941        Type.UINT128,
3942        Type.UINT256,
3943        Type.UMEDIUMINT,
3944        Type.USMALLINT,
3945        Type.UTINYINT,
3946    }
3947
3948    INTEGER_TYPES = {
3949        *SIGNED_INTEGER_TYPES,
3950        *UNSIGNED_INTEGER_TYPES,
3951        Type.BIT,
3952    }
3953
3954    FLOAT_TYPES = {
3955        Type.DOUBLE,
3956        Type.FLOAT,
3957    }
3958
3959    REAL_TYPES = {
3960        *FLOAT_TYPES,
3961        Type.BIGDECIMAL,
3962        Type.DECIMAL,
3963        Type.MONEY,
3964        Type.SMALLMONEY,
3965        Type.UDECIMAL,
3966    }
3967
3968    NUMERIC_TYPES = {
3969        *INTEGER_TYPES,
3970        *REAL_TYPES,
3971    }
3972
3973    TEMPORAL_TYPES = {
3974        Type.DATE,
3975        Type.DATE32,
3976        Type.DATETIME,
3977        Type.DATETIME64,
3978        Type.TIME,
3979        Type.TIMESTAMP,
3980        Type.TIMESTAMPLTZ,
3981        Type.TIMESTAMPTZ,
3982        Type.TIMESTAMP_MS,
3983        Type.TIMESTAMP_NS,
3984        Type.TIMESTAMP_S,
3985        Type.TIMETZ,
3986    }
3987
3988    @classmethod
3989    def build(
3990        cls,
3991        dtype: DATA_TYPE,
3992        dialect: DialectType = None,
3993        udt: bool = False,
3994        copy: bool = True,
3995        **kwargs,
3996    ) -> DataType:
3997        """
3998        Constructs a DataType object.
3999
4000        Args:
4001            dtype: the data type of interest.
4002            dialect: the dialect to use for parsing `dtype`, in case it's a string.
4003            udt: when set to True, `dtype` will be used as-is if it can't be parsed into a
4004                DataType, thus creating a user-defined type.
4005            copy: whether to copy the data type.
4006            kwargs: additional arguments to pass in the constructor of DataType.
4007
4008        Returns:
4009            The constructed DataType object.
4010        """
4011        from sqlglot import parse_one
4012
4013        if isinstance(dtype, str):
4014            if dtype.upper() == "UNKNOWN":
4015                return DataType(this=DataType.Type.UNKNOWN, **kwargs)
4016
4017            try:
4018                data_type_exp = parse_one(
4019                    dtype, read=dialect, into=DataType, error_level=ErrorLevel.IGNORE
4020                )
4021            except ParseError:
4022                if udt:
4023                    return DataType(this=DataType.Type.USERDEFINED, kind=dtype, **kwargs)
4024                raise
4025        elif isinstance(dtype, DataType.Type):
4026            data_type_exp = DataType(this=dtype)
4027        elif isinstance(dtype, DataType):
4028            return maybe_copy(dtype, copy)
4029        else:
4030            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
4031
4032        return DataType(**{**data_type_exp.args, **kwargs})
4033
4034    def is_type(self, *dtypes: DATA_TYPE) -> bool:
4035        """
4036        Checks whether this DataType matches one of the provided data types. Nested types or precision
4037        will be compared using "structural equivalence" semantics, so e.g. array<int> != array<float>.
4038
4039        Args:
4040            dtypes: the data types to compare this DataType to.
4041
4042        Returns:
4043            True, if and only if there is a type in `dtypes` which is equal to this DataType.
4044        """
4045        for dtype in dtypes:
4046            other = DataType.build(dtype, copy=False, udt=True)
4047
4048            if (
4049                other.expressions
4050                or self.this == DataType.Type.USERDEFINED
4051                or other.this == DataType.Type.USERDEFINED
4052            ):
4053                matches = self == other
4054            else:
4055                matches = self.this == other.this
4056
4057            if matches:
4058                return True
4059        return False
4060
4061
4062DATA_TYPE = t.Union[str, DataType, DataType.Type]
4063
4064
4065# https://www.postgresql.org/docs/15/datatype-pseudo.html
4066class PseudoType(DataType):
4067    arg_types = {"this": True}
4068
4069
4070# https://www.postgresql.org/docs/15/datatype-oid.html
4071class ObjectIdentifier(DataType):
4072    arg_types = {"this": True}
4073
4074
4075# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
4076class SubqueryPredicate(Predicate):
4077    pass
4078
4079
4080class All(SubqueryPredicate):
4081    pass
4082
4083
4084class Any(SubqueryPredicate):
4085    pass
4086
4087
4088class Exists(SubqueryPredicate):
4089    pass
4090
4091
4092# Commands to interact with the databases or engines. For most of the command
4093# expressions we parse whatever comes after the command's name as a string.
4094class Command(Expression):
4095    arg_types = {"this": True, "expression": False}
4096
4097
4098class Transaction(Expression):
4099    arg_types = {"this": False, "modes": False, "mark": False}
4100
4101
4102class Commit(Expression):
4103    arg_types = {"chain": False, "this": False, "durability": False}
4104
4105
4106class Rollback(Expression):
4107    arg_types = {"savepoint": False, "this": False}
4108
4109
4110class AlterTable(Expression):
4111    arg_types = {
4112        "this": True,
4113        "actions": True,
4114        "exists": False,
4115        "only": False,
4116        "options": False,
4117    }
4118
4119
4120class AddConstraint(Expression):
4121    arg_types = {"expressions": True}
4122
4123
4124class DropPartition(Expression):
4125    arg_types = {"expressions": True, "exists": False}
4126
4127
4128# Binary expressions like (ADD a b)
4129class Binary(Condition):
4130    arg_types = {"this": True, "expression": True}
4131
4132    @property
4133    def left(self) -> Expression:
4134        return self.this
4135
4136    @property
4137    def right(self) -> Expression:
4138        return self.expression
4139
4140
4141class Add(Binary):
4142    pass
4143
4144
4145class Connector(Binary):
4146    pass
4147
4148
4149class And(Connector):
4150    pass
4151
4152
4153class Or(Connector):
4154    pass
4155
4156
4157class BitwiseAnd(Binary):
4158    pass
4159
4160
4161class BitwiseLeftShift(Binary):
4162    pass
4163
4164
4165class BitwiseOr(Binary):
4166    pass
4167
4168
4169class BitwiseRightShift(Binary):
4170    pass
4171
4172
4173class BitwiseXor(Binary):
4174    pass
4175
4176
4177class Div(Binary):
4178    arg_types = {"this": True, "expression": True, "typed": False, "safe": False}
4179
4180
4181class Overlaps(Binary):
4182    pass
4183
4184
4185class Dot(Binary):
4186    @property
4187    def is_star(self) -> bool:
4188        return self.expression.is_star
4189
4190    @property
4191    def name(self) -> str:
4192        return self.expression.name
4193
4194    @property
4195    def output_name(self) -> str:
4196        return self.name
4197
4198    @classmethod
4199    def build(self, expressions: t.Sequence[Expression]) -> Dot:
4200        """Build a Dot object with a sequence of expressions."""
4201        if len(expressions) < 2:
4202            raise ValueError("Dot requires >= 2 expressions.")
4203
4204        return t.cast(Dot, reduce(lambda x, y: Dot(this=x, expression=y), expressions))
4205
4206    @property
4207    def parts(self) -> t.List[Expression]:
4208        """Return the parts of a table / column in order catalog, db, table."""
4209        this, *parts = self.flatten()
4210
4211        parts.reverse()
4212
4213        for arg in ("this", "table", "db", "catalog"):
4214            part = this.args.get(arg)
4215
4216            if isinstance(part, Expression):
4217                parts.append(part)
4218
4219        parts.reverse()
4220        return parts
4221
4222
4223class DPipe(Binary):
4224    arg_types = {"this": True, "expression": True, "safe": False}
4225
4226
4227class EQ(Binary, Predicate):
4228    pass
4229
4230
4231class NullSafeEQ(Binary, Predicate):
4232    pass
4233
4234
4235class NullSafeNEQ(Binary, Predicate):
4236    pass
4237
4238
4239# Represents e.g. := in DuckDB which is mostly used for setting parameters
4240class PropertyEQ(Binary):
4241    pass
4242
4243
4244class Distance(Binary):
4245    pass
4246
4247
4248class Escape(Binary):
4249    pass
4250
4251
4252class Glob(Binary, Predicate):
4253    pass
4254
4255
4256class GT(Binary, Predicate):
4257    pass
4258
4259
4260class GTE(Binary, Predicate):
4261    pass
4262
4263
4264class ILike(Binary, Predicate):
4265    pass
4266
4267
4268class ILikeAny(Binary, Predicate):
4269    pass
4270
4271
4272class IntDiv(Binary):
4273    pass
4274
4275
4276class Is(Binary, Predicate):
4277    pass
4278
4279
4280class Kwarg(Binary):
4281    """Kwarg in special functions like func(kwarg => y)."""
4282
4283
4284class Like(Binary, Predicate):
4285    pass
4286
4287
4288class LikeAny(Binary, Predicate):
4289    pass
4290
4291
4292class LT(Binary, Predicate):
4293    pass
4294
4295
4296class LTE(Binary, Predicate):
4297    pass
4298
4299
4300class Mod(Binary):
4301    pass
4302
4303
4304class Mul(Binary):
4305    pass
4306
4307
4308class NEQ(Binary, Predicate):
4309    pass
4310
4311
4312# https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH
4313class Operator(Binary):
4314    arg_types = {"this": True, "operator": True, "expression": True}
4315
4316
4317class SimilarTo(Binary, Predicate):
4318    pass
4319
4320
4321class Slice(Binary):
4322    arg_types = {"this": False, "expression": False}
4323
4324
4325class Sub(Binary):
4326    pass
4327
4328
4329# Unary Expressions
4330# (NOT a)
4331class Unary(Condition):
4332    pass
4333
4334
4335class BitwiseNot(Unary):
4336    pass
4337
4338
4339class Not(Unary):
4340    pass
4341
4342
4343class Paren(Unary):
4344    @property
4345    def output_name(self) -> str:
4346        return self.this.name
4347
4348
4349class Neg(Unary):
4350    pass
4351
4352
4353class Alias(Expression):
4354    arg_types = {"this": True, "alias": False}
4355
4356    @property
4357    def output_name(self) -> str:
4358        return self.alias
4359
4360
4361# BigQuery requires the UNPIVOT column list aliases to be either strings or ints, but
4362# other dialects require identifiers. This enables us to transpile between them easily.
4363class PivotAlias(Alias):
4364    pass
4365
4366
4367class Aliases(Expression):
4368    arg_types = {"this": True, "expressions": True}
4369
4370    @property
4371    def aliases(self):
4372        return self.expressions
4373
4374
4375# https://docs.aws.amazon.com/redshift/latest/dg/query-super.html
4376class AtIndex(Expression):
4377    arg_types = {"this": True, "expression": True}
4378
4379
4380class AtTimeZone(Expression):
4381    arg_types = {"this": True, "zone": True}
4382
4383
4384class FromTimeZone(Expression):
4385    arg_types = {"this": True, "zone": True}
4386
4387
4388class Between(Predicate):
4389    arg_types = {"this": True, "low": True, "high": True}
4390
4391
4392class Bracket(Condition):
4393    # https://cloud.google.com/bigquery/docs/reference/standard-sql/operators#array_subscript_operator
4394    arg_types = {"this": True, "expressions": True, "offset": False, "safe": False}
4395
4396    @property
4397    def output_name(self) -> str:
4398        if len(self.expressions) == 1:
4399            return self.expressions[0].output_name
4400
4401        return super().output_name
4402
4403
4404class Distinct(Expression):
4405    arg_types = {"expressions": False, "on": False}
4406
4407
4408class In(Predicate):
4409    arg_types = {
4410        "this": True,
4411        "expressions": False,
4412        "query": False,
4413        "unnest": False,
4414        "field": False,
4415        "is_global": False,
4416    }
4417
4418
4419# https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language#for-in
4420class ForIn(Expression):
4421    arg_types = {"this": True, "expression": True}
4422
4423
4424class TimeUnit(Expression):
4425    """Automatically converts unit arg into a var."""
4426
4427    arg_types = {"unit": False}
4428
4429    UNABBREVIATED_UNIT_NAME = {
4430        "D": "DAY",
4431        "H": "HOUR",
4432        "M": "MINUTE",
4433        "MS": "MILLISECOND",
4434        "NS": "NANOSECOND",
4435        "Q": "QUARTER",
4436        "S": "SECOND",
4437        "US": "MICROSECOND",
4438        "W": "WEEK",
4439        "Y": "YEAR",
4440    }
4441
4442    VAR_LIKE = (Column, Literal, Var)
4443
4444    def __init__(self, **args):
4445        unit = args.get("unit")
4446        if isinstance(unit, self.VAR_LIKE):
4447            args["unit"] = Var(
4448                this=(self.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name).upper()
4449            )
4450        elif isinstance(unit, Week):
4451            unit.set("this", Var(this=unit.this.name.upper()))
4452
4453        super().__init__(**args)
4454
4455    @property
4456    def unit(self) -> t.Optional[Var | IntervalSpan]:
4457        return self.args.get("unit")
4458
4459
4460class IntervalOp(TimeUnit):
4461    arg_types = {"unit": True, "expression": True}
4462
4463    def interval(self):
4464        return Interval(
4465            this=self.expression.copy(),
4466            unit=self.unit.copy(),
4467        )
4468
4469
4470# https://www.oracletutorial.com/oracle-basics/oracle-interval/
4471# https://trino.io/docs/current/language/types.html#interval-day-to-second
4472# https://docs.databricks.com/en/sql/language-manual/data-types/interval-type.html
4473class IntervalSpan(DataType):
4474    arg_types = {"this": True, "expression": True}
4475
4476
4477class Interval(TimeUnit):
4478    arg_types = {"this": False, "unit": False}
4479
4480
4481class IgnoreNulls(Expression):
4482    pass
4483
4484
4485class RespectNulls(Expression):
4486    pass
4487
4488
4489# https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate-function-calls#max_min_clause
4490class HavingMax(Expression):
4491    arg_types = {"this": True, "expression": True, "max": True}
4492
4493
4494# Functions
4495class Func(Condition):
4496    """
4497    The base class for all function expressions.
4498
4499    Attributes:
4500        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
4501            treated as a variable length argument and the argument's value will be stored as a list.
4502        _sql_names (list): the SQL name (1st item in the list) and aliases (subsequent items) for this
4503            function expression. These values are used to map this node to a name during parsing as
4504            well as to provide the function's name during SQL string generation. By default the SQL
4505            name is set to the expression's class name transformed to snake case.
4506    """
4507
4508    is_var_len_args = False
4509
4510    @classmethod
4511    def from_arg_list(cls, args):
4512        if cls.is_var_len_args:
4513            all_arg_keys = list(cls.arg_types)
4514            # If this function supports variable length argument treat the last argument as such.
4515            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
4516            num_non_var = len(non_var_len_arg_keys)
4517
4518            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
4519            args_dict[all_arg_keys[-1]] = args[num_non_var:]
4520        else:
4521            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
4522
4523        return cls(**args_dict)
4524
4525    @classmethod
4526    def sql_names(cls):
4527        if cls is Func:
4528            raise NotImplementedError(
4529                "SQL name is only supported by concrete function implementations"
4530            )
4531        if "_sql_names" not in cls.__dict__:
4532            cls._sql_names = [camel_to_snake_case(cls.__name__)]
4533        return cls._sql_names
4534
4535    @classmethod
4536    def sql_name(cls):
4537        return cls.sql_names()[0]
4538
4539    @classmethod
4540    def default_parser_mappings(cls):
4541        return {name: cls.from_arg_list for name in cls.sql_names()}
4542
4543
4544class AggFunc(Func):
4545    pass
4546
4547
4548class ParameterizedAgg(AggFunc):
4549    arg_types = {"this": True, "expressions": True, "params": True}
4550
4551
4552class Abs(Func):
4553    pass
4554
4555
4556class ArgMax(AggFunc):
4557    arg_types = {"this": True, "expression": True, "count": False}
4558    _sql_names = ["ARG_MAX", "ARGMAX", "MAX_BY"]
4559
4560
4561class ArgMin(AggFunc):
4562    arg_types = {"this": True, "expression": True, "count": False}
4563    _sql_names = ["ARG_MIN", "ARGMIN", "MIN_BY"]
4564
4565
4566class ApproxTopK(AggFunc):
4567    arg_types = {"this": True, "expression": False, "counters": False}
4568
4569
4570class Flatten(Func):
4571    pass
4572
4573
4574# https://spark.apache.org/docs/latest/api/sql/index.html#transform
4575class Transform(Func):
4576    arg_types = {"this": True, "expression": True}
4577
4578
4579class Anonymous(Func):
4580    arg_types = {"this": True, "expressions": False}
4581    is_var_len_args = True
4582
4583    @property
4584    def name(self) -> str:
4585        return self.this if isinstance(self.this, str) else self.this.name
4586
4587
4588class AnonymousAggFunc(AggFunc):
4589    arg_types = {"this": True, "expressions": False}
4590    is_var_len_args = True
4591
4592
4593# https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators
4594class CombinedAggFunc(AnonymousAggFunc):
4595    arg_types = {"this": True, "expressions": False, "parts": True}
4596
4597
4598class CombinedParameterizedAgg(ParameterizedAgg):
4599    arg_types = {"this": True, "expressions": True, "params": True, "parts": True}
4600
4601
4602# https://docs.snowflake.com/en/sql-reference/functions/hll
4603# https://docs.aws.amazon.com/redshift/latest/dg/r_HLL_function.html
4604class Hll(AggFunc):
4605    arg_types = {"this": True, "expressions": False}
4606    is_var_len_args = True
4607
4608
4609class ApproxDistinct(AggFunc):
4610    arg_types = {"this": True, "accuracy": False}
4611    _sql_names = ["APPROX_DISTINCT", "APPROX_COUNT_DISTINCT"]
4612
4613
4614class Array(Func):
4615    arg_types = {"expressions": False}
4616    is_var_len_args = True
4617
4618
4619# https://docs.snowflake.com/en/sql-reference/functions/to_array
4620class ToArray(Func):
4621    pass
4622
4623
4624# https://docs.snowflake.com/en/sql-reference/functions/to_char
4625# https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/TO_CHAR-number.html
4626class ToChar(Func):
4627    arg_types = {"this": True, "format": False, "nlsparam": False}
4628
4629
4630# https://docs.snowflake.com/en/sql-reference/functions/to_decimal
4631# https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/TO_NUMBER.html
4632class ToNumber(Func):
4633    arg_types = {
4634        "this": True,
4635        "format": False,
4636        "nlsparam": False,
4637        "precision": False,
4638        "scale": False,
4639    }
4640
4641
4642# https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16#syntax
4643class Convert(Func):
4644    arg_types = {"this": True, "expression": True, "style": False}
4645
4646
4647class GenerateSeries(Func):
4648    arg_types = {"start": True, "end": True, "step": False, "is_end_exclusive": False}
4649
4650
4651class ArrayAgg(AggFunc):
4652    pass
4653
4654
4655class ArrayUniqueAgg(AggFunc):
4656    pass
4657
4658
4659class ArrayAll(Func):
4660    arg_types = {"this": True, "expression": True}
4661
4662
4663# Represents Python's `any(f(x) for x in array)`, where `array` is `this` and `f` is `expression`
4664class ArrayAny(Func):
4665    arg_types = {"this": True, "expression": True}
4666
4667
4668class ArrayConcat(Func):
4669    _sql_names = ["ARRAY_CONCAT", "ARRAY_CAT"]
4670    arg_types = {"this": True, "expressions": False}
4671    is_var_len_args = True
4672
4673
4674class ArrayContains(Binary, Func):
4675    pass
4676
4677
4678class ArrayContained(Binary):
4679    pass
4680
4681
4682class ArrayFilter(Func):
4683    arg_types = {"this": True, "expression": True}
4684    _sql_names = ["FILTER", "ARRAY_FILTER"]
4685
4686
4687class ArrayToString(Func):
4688    arg_types = {"this": True, "expression": True, "null": False}
4689    _sql_names = ["ARRAY_TO_STRING", "ARRAY_JOIN"]
4690
4691
4692class ArrayOverlaps(Binary, Func):
4693    pass
4694
4695
4696class ArraySize(Func):
4697    arg_types = {"this": True, "expression": False}
4698    _sql_names = ["ARRAY_SIZE", "ARRAY_LENGTH"]
4699
4700
4701class ArraySort(Func):
4702    arg_types = {"this": True, "expression": False}
4703
4704
4705class ArraySum(Func):
4706    arg_types = {"this": True, "expression": False}
4707
4708
4709class ArrayUnionAgg(AggFunc):
4710    pass
4711
4712
4713class Avg(AggFunc):
4714    pass
4715
4716
4717class AnyValue(AggFunc):
4718    pass
4719
4720
4721class Lag(AggFunc):
4722    arg_types = {"this": True, "offset": False, "default": False}
4723
4724
4725class Lead(AggFunc):
4726    arg_types = {"this": True, "offset": False, "default": False}
4727
4728
4729# some dialects have a distinction between first and first_value, usually first is an aggregate func
4730# and first_value is a window func
4731class First(AggFunc):
4732    pass
4733
4734
4735class Last(AggFunc):
4736    pass
4737
4738
4739class FirstValue(AggFunc):
4740    pass
4741
4742
4743class LastValue(AggFunc):
4744    pass
4745
4746
4747class NthValue(AggFunc):
4748    arg_types = {"this": True, "offset": True}
4749
4750
4751class Case(Func):
4752    arg_types = {"this": False, "ifs": True, "default": False}
4753
4754    def when(self, condition: ExpOrStr, then: ExpOrStr, copy: bool = True, **opts) -> Case:
4755        instance = maybe_copy(self, copy)
4756        instance.append(
4757            "ifs",
4758            If(
4759                this=maybe_parse(condition, copy=copy, **opts),
4760                true=maybe_parse(then, copy=copy, **opts),
4761            ),
4762        )
4763        return instance
4764
4765    def else_(self, condition: ExpOrStr, copy: bool = True, **opts) -> Case:
4766        instance = maybe_copy(self, copy)
4767        instance.set("default", maybe_parse(condition, copy=copy, **opts))
4768        return instance
4769
4770
4771class Cast(Func):
4772    arg_types = {
4773        "this": True,
4774        "to": True,
4775        "format": False,
4776        "safe": False,
4777        "action": False,
4778    }
4779
4780    @property
4781    def name(self) -> str:
4782        return self.this.name
4783
4784    @property
4785    def to(self) -> DataType:
4786        return self.args["to"]
4787
4788    @property
4789    def output_name(self) -> str:
4790        return self.name
4791
4792    def is_type(self, *dtypes: DATA_TYPE) -> bool:
4793        """
4794        Checks whether this Cast's DataType matches one of the provided data types. Nested types
4795        like arrays or structs will be compared using "structural equivalence" semantics, so e.g.
4796        array<int> != array<float>.
4797
4798        Args:
4799            dtypes: the data types to compare this Cast's DataType to.
4800
4801        Returns:
4802            True, if and only if there is a type in `dtypes` which is equal to this Cast's DataType.
4803        """
4804        return self.to.is_type(*dtypes)
4805
4806
4807class TryCast(Cast):
4808    pass
4809
4810
4811class CastToStrType(Func):
4812    arg_types = {"this": True, "to": True}
4813
4814
4815class Collate(Binary, Func):
4816    pass
4817
4818
4819class Ceil(Func):
4820    arg_types = {"this": True, "decimals": False}
4821    _sql_names = ["CEIL", "CEILING"]
4822
4823
4824class Coalesce(Func):
4825    arg_types = {"this": True, "expressions": False}
4826    is_var_len_args = True
4827    _sql_names = ["COALESCE", "IFNULL", "NVL"]
4828
4829
4830class Chr(Func):
4831    arg_types = {"this": True, "charset": False, "expressions": False}
4832    is_var_len_args = True
4833    _sql_names = ["CHR", "CHAR"]
4834
4835
4836class Concat(Func):
4837    arg_types = {"expressions": True, "safe": False, "coalesce": False}
4838    is_var_len_args = True
4839
4840
4841class ConcatWs(Concat):
4842    _sql_names = ["CONCAT_WS"]
4843
4844
4845# https://docs.oracle.com/cd/B13789_01/server.101/b10759/operators004.htm#i1035022
4846class ConnectByRoot(Func):
4847    pass
4848
4849
4850class Count(AggFunc):
4851    arg_types = {"this": False, "expressions": False}
4852    is_var_len_args = True
4853
4854
4855class CountIf(AggFunc):
4856    _sql_names = ["COUNT_IF", "COUNTIF"]
4857
4858
4859# cube root
4860class Cbrt(Func):
4861    pass
4862
4863
4864class CurrentDate(Func):
4865    arg_types = {"this": False}
4866
4867
4868class CurrentDatetime(Func):
4869    arg_types = {"this": False}
4870
4871
4872class CurrentTime(Func):
4873    arg_types = {"this": False}
4874
4875
4876class CurrentTimestamp(Func):
4877    arg_types = {"this": False, "transaction": False}
4878
4879
4880class CurrentUser(Func):
4881    arg_types = {"this": False}
4882
4883
4884class DateAdd(Func, IntervalOp):
4885    arg_types = {"this": True, "expression": True, "unit": False}
4886
4887
4888class DateSub(Func, IntervalOp):
4889    arg_types = {"this": True, "expression": True, "unit": False}
4890
4891
4892class DateDiff(Func, TimeUnit):
4893    _sql_names = ["DATEDIFF", "DATE_DIFF"]
4894    arg_types = {"this": True, "expression": True, "unit": False}
4895
4896
4897class DateTrunc(Func):
4898    arg_types = {"unit": True, "this": True, "zone": False}
4899
4900    def __init__(self, **args):
4901        unit = args.get("unit")
4902        if isinstance(unit, TimeUnit.VAR_LIKE):
4903            args["unit"] = Literal.string(
4904                (TimeUnit.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name).upper()
4905            )
4906        elif isinstance(unit, Week):
4907            unit.set("this", Literal.string(unit.this.name.upper()))
4908
4909        super().__init__(**args)
4910
4911    @property
4912    def unit(self) -> Expression:
4913        return self.args["unit"]
4914
4915
4916class DatetimeAdd(Func, IntervalOp):
4917    arg_types = {"this": True, "expression": True, "unit": False}
4918
4919
4920class DatetimeSub(Func, IntervalOp):
4921    arg_types = {"this": True, "expression": True, "unit": False}
4922
4923
4924class DatetimeDiff(Func, TimeUnit):
4925    arg_types = {"this": True, "expression": True, "unit": False}
4926
4927
4928class DatetimeTrunc(Func, TimeUnit):
4929    arg_types = {"this": True, "unit": True, "zone": False}
4930
4931
4932class DayOfWeek(Func):
4933    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
4934
4935
4936class DayOfMonth(Func):
4937    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
4938
4939
4940class DayOfYear(Func):
4941    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
4942
4943
4944class ToDays(Func):
4945    pass
4946
4947
4948class WeekOfYear(Func):
4949    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
4950
4951
4952class MonthsBetween(Func):
4953    arg_types = {"this": True, "expression": True, "roundoff": False}
4954
4955
4956class LastDay(Func, TimeUnit):
4957    _sql_names = ["LAST_DAY", "LAST_DAY_OF_MONTH"]
4958    arg_types = {"this": True, "unit": False}
4959
4960
4961class Extract(Func):
4962    arg_types = {"this": True, "expression": True}
4963
4964
4965class Timestamp(Func):
4966    arg_types = {"this": False, "expression": False, "with_tz": False}
4967
4968
4969class TimestampAdd(Func, TimeUnit):
4970    arg_types = {"this": True, "expression": True, "unit": False}
4971
4972
4973class TimestampSub(Func, TimeUnit):
4974    arg_types = {"this": True, "expression": True, "unit": False}
4975
4976
4977class TimestampDiff(Func, TimeUnit):
4978    _sql_names = ["TIMESTAMPDIFF", "TIMESTAMP_DIFF"]
4979    arg_types = {"this": True, "expression": True, "unit": False}
4980
4981
4982class TimestampTrunc(Func, TimeUnit):
4983    arg_types = {"this": True, "unit": True, "zone": False}
4984
4985
4986class TimeAdd(Func, TimeUnit):
4987    arg_types = {"this": True, "expression": True, "unit": False}
4988
4989
4990class TimeSub(Func, TimeUnit):
4991    arg_types = {"this": True, "expression": True, "unit": False}
4992
4993
4994class TimeDiff(Func, TimeUnit):
4995    arg_types = {"this": True, "expression": True, "unit": False}
4996
4997
4998class TimeTrunc(Func, TimeUnit):
4999    arg_types = {"this": True, "unit": True, "zone": False}
5000
5001
5002class DateFromParts(Func):
5003    _sql_names = ["DATE_FROM_PARTS", "DATEFROMPARTS"]
5004    arg_types = {"year": True, "month": True, "day": True}
5005
5006
5007class TimeFromParts(Func):
5008    _sql_names = ["TIME_FROM_PARTS", "TIMEFROMPARTS"]
5009    arg_types = {
5010        "hour": True,
5011        "min": True,
5012        "sec": True,
5013        "nano": False,
5014        "fractions": False,
5015        "precision": False,
5016    }
5017
5018
5019class DateStrToDate(Func):
5020    pass
5021
5022
5023class DateToDateStr(Func):
5024    pass
5025
5026
5027class DateToDi(Func):
5028    pass
5029
5030
5031# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date
5032class Date(Func):
5033    arg_types = {"this": False, "zone": False, "expressions": False}
5034    is_var_len_args = True
5035
5036
5037class Day(Func):
5038    pass
5039
5040
5041class Decode(Func):
5042    arg_types = {"this": True, "charset": True, "replace": False}
5043
5044
5045class DiToDate(Func):
5046    pass
5047
5048
5049class Encode(Func):
5050    arg_types = {"this": True, "charset": True}
5051
5052
5053class Exp(Func):
5054    pass
5055
5056
5057# https://docs.snowflake.com/en/sql-reference/functions/flatten
5058class Explode(Func):
5059    arg_types = {"this": True, "expressions": False}
5060    is_var_len_args = True
5061
5062
5063class ExplodeOuter(Explode):
5064    pass
5065
5066
5067class Posexplode(Explode):
5068    pass
5069
5070
5071class PosexplodeOuter(Posexplode, ExplodeOuter):
5072    pass
5073
5074
5075class Floor(Func):
5076    arg_types = {"this": True, "decimals": False}
5077
5078
5079class FromBase64(Func):
5080    pass
5081
5082
5083class ToBase64(Func):
5084    pass
5085
5086
5087class GenerateDateArray(Func):
5088    arg_types = {"start": True, "end": True, "interval": False}
5089
5090
5091class Greatest(Func):
5092    arg_types = {"this": True, "expressions": False}
5093    is_var_len_args = True
5094
5095
5096class GroupConcat(AggFunc):
5097    arg_types = {"this": True, "separator": False}
5098
5099
5100class Hex(Func):
5101    pass
5102
5103
5104class Xor(Connector, Func):
5105    arg_types = {"this": False, "expression": False, "expressions": False}
5106
5107
5108class If(Func):
5109    arg_types = {"this": True, "true": True, "false": False}
5110    _sql_names = ["IF", "IIF"]
5111
5112
5113class Nullif(Func):
5114    arg_types = {"this": True, "expression": True}
5115
5116
5117class Initcap(Func):
5118    arg_types = {"this": True, "expression": False}
5119
5120
5121class IsNan(Func):
5122    _sql_names = ["IS_NAN", "ISNAN"]
5123
5124
5125class IsInf(Func):
5126    _sql_names = ["IS_INF", "ISINF"]
5127
5128
5129class JSONPath(Expression):
5130    arg_types = {"expressions": True}
5131
5132    @property
5133    def output_name(self) -> str:
5134        last_segment = self.expressions[-1].this
5135        return last_segment if isinstance(last_segment, str) else ""
5136
5137
5138class JSONPathPart(Expression):
5139    arg_types = {}
5140
5141
5142class JSONPathFilter(JSONPathPart):
5143    arg_types = {"this": True}
5144
5145
5146class JSONPathKey(JSONPathPart):
5147    arg_types = {"this": True}
5148
5149
5150class JSONPathRecursive(JSONPathPart):
5151    arg_types = {"this": False}
5152
5153
5154class JSONPathRoot(JSONPathPart):
5155    pass
5156
5157
5158class JSONPathScript(JSONPathPart):
5159    arg_types = {"this": True}
5160
5161
5162class JSONPathSlice(JSONPathPart):
5163    arg_types = {"start": False, "end": False, "step": False}
5164
5165
5166class JSONPathSelector(JSONPathPart):
5167    arg_types = {"this": True}
5168
5169
5170class JSONPathSubscript(JSONPathPart):
5171    arg_types = {"this": True}
5172
5173
5174class JSONPathUnion(JSONPathPart):
5175    arg_types = {"expressions": True}
5176
5177
5178class JSONPathWildcard(JSONPathPart):
5179    pass
5180
5181
5182class FormatJson(Expression):
5183    pass
5184
5185
5186class JSONKeyValue(Expression):
5187    arg_types = {"this": True, "expression": True}
5188
5189
5190class JSONObject(Func):
5191    arg_types = {
5192        "expressions": False,
5193        "null_handling": False,
5194        "unique_keys": False,
5195        "return_type": False,
5196        "encoding": False,
5197    }
5198
5199
5200class JSONObjectAgg(AggFunc):
5201    arg_types = {
5202        "expressions": False,
5203        "null_handling": False,
5204        "unique_keys": False,
5205        "return_type": False,
5206        "encoding": False,
5207    }
5208
5209
5210# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAY.html
5211class JSONArray(Func):
5212    arg_types = {
5213        "expressions": True,
5214        "null_handling": False,
5215        "return_type": False,
5216        "strict": False,
5217    }
5218
5219
5220# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAYAGG.html
5221class JSONArrayAgg(Func):
5222    arg_types = {
5223        "this": True,
5224        "order": False,
5225        "null_handling": False,
5226        "return_type": False,
5227        "strict": False,
5228    }
5229
5230
5231# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
5232# Note: parsing of JSON column definitions is currently incomplete.
5233class JSONColumnDef(Expression):
5234    arg_types = {"this": False, "kind": False, "path": False, "nested_schema": False}
5235
5236
5237class JSONSchema(Expression):
5238    arg_types = {"expressions": True}
5239
5240
5241# # https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
5242class JSONTable(Func):
5243    arg_types = {
5244        "this": True,
5245        "schema": True,
5246        "path": False,
5247        "error_handling": False,
5248        "empty_handling": False,
5249    }
5250
5251
5252class OpenJSONColumnDef(Expression):
5253    arg_types = {"this": True, "kind": True, "path": False, "as_json": False}
5254
5255
5256class OpenJSON(Func):
5257    arg_types = {"this": True, "path": False, "expressions": False}
5258
5259
5260class JSONBContains(Binary):
5261    _sql_names = ["JSONB_CONTAINS"]
5262
5263
5264class JSONExtract(Binary, Func):
5265    arg_types = {"this": True, "expression": True, "only_json_types": False, "expressions": False}
5266    _sql_names = ["JSON_EXTRACT"]
5267    is_var_len_args = True
5268
5269    @property
5270    def output_name(self) -> str:
5271        return self.expression.output_name if not self.expressions else ""
5272
5273
5274class JSONExtractScalar(Binary, Func):
5275    arg_types = {"this": True, "expression": True, "only_json_types": False, "expressions": False}
5276    _sql_names = ["JSON_EXTRACT_SCALAR"]
5277    is_var_len_args = True
5278
5279    @property
5280    def output_name(self) -> str:
5281        return self.expression.output_name
5282
5283
5284class JSONBExtract(Binary, Func):
5285    _sql_names = ["JSONB_EXTRACT"]
5286
5287
5288class JSONBExtractScalar(Binary, Func):
5289    _sql_names = ["JSONB_EXTRACT_SCALAR"]
5290
5291
5292class JSONFormat(Func):
5293    arg_types = {"this": False, "options": False}
5294    _sql_names = ["JSON_FORMAT"]
5295
5296
5297# https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_member-of
5298class JSONArrayContains(Binary, Predicate, Func):
5299    _sql_names = ["JSON_ARRAY_CONTAINS"]
5300
5301
5302class ParseJSON(Func):
5303    # BigQuery, Snowflake have PARSE_JSON, Presto has JSON_PARSE
5304    _sql_names = ["PARSE_JSON", "JSON_PARSE"]
5305    arg_types = {"this": True, "expressions": False}
5306    is_var_len_args = True
5307
5308
5309class Least(Func):
5310    arg_types = {"this": True, "expressions": False}
5311    is_var_len_args = True
5312
5313
5314class Left(Func):
5315    arg_types = {"this": True, "expression": True}
5316
5317
5318class Right(Func):
5319    arg_types = {"this": True, "expression": True}
5320
5321
5322class Length(Func):
5323    _sql_names = ["LENGTH", "LEN"]
5324
5325
5326class Levenshtein(Func):
5327    arg_types = {
5328        "this": True,
5329        "expression": False,
5330        "ins_cost": False,
5331        "del_cost": False,
5332        "sub_cost": False,
5333    }
5334
5335
5336class Ln(Func):
5337    pass
5338
5339
5340class Log(Func):
5341    arg_types = {"this": True, "expression": False}
5342
5343
5344class LogicalOr(AggFunc):
5345    _sql_names = ["LOGICAL_OR", "BOOL_OR", "BOOLOR_AGG"]
5346
5347
5348class LogicalAnd(AggFunc):
5349    _sql_names = ["LOGICAL_AND", "BOOL_AND", "BOOLAND_AGG"]
5350
5351
5352class Lower(Func):
5353    _sql_names = ["LOWER", "LCASE"]
5354
5355
5356class Map(Func):
5357    arg_types = {"keys": False, "values": False}
5358
5359    @property
5360    def keys(self) -> t.List[Expression]:
5361        keys = self.args.get("keys")
5362        return keys.expressions if keys else []
5363
5364    @property
5365    def values(self) -> t.List[Expression]:
5366        values = self.args.get("values")
5367        return values.expressions if values else []
5368
5369
5370# Represents the MAP {...} syntax in DuckDB - basically convert a struct to a MAP
5371class ToMap(Func):
5372    pass
5373
5374
5375class MapFromEntries(Func):
5376    pass
5377
5378
5379class StarMap(Func):
5380    pass
5381
5382
5383class VarMap(Func):
5384    arg_types = {"keys": True, "values": True}
5385    is_var_len_args = True
5386
5387    @property
5388    def keys(self) -> t.List[Expression]:
5389        return self.args["keys"].expressions
5390
5391    @property
5392    def values(self) -> t.List[Expression]:
5393        return self.args["values"].expressions
5394
5395
5396# https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
5397class MatchAgainst(Func):
5398    arg_types = {"this": True, "expressions": True, "modifier": False}
5399
5400
5401class Max(AggFunc):
5402    arg_types = {"this": True, "expressions": False}
5403    is_var_len_args = True
5404
5405
5406class MD5(Func):
5407    _sql_names = ["MD5"]
5408
5409
5410# Represents the variant of the MD5 function that returns a binary value
5411class MD5Digest(Func):
5412    _sql_names = ["MD5_DIGEST"]
5413
5414
5415class Min(AggFunc):
5416    arg_types = {"this": True, "expressions": False}
5417    is_var_len_args = True
5418
5419
5420class Month(Func):
5421    pass
5422
5423
5424class AddMonths(Func):
5425    arg_types = {"this": True, "expression": True}
5426
5427
5428class Nvl2(Func):
5429    arg_types = {"this": True, "true": True, "false": False}
5430
5431
5432# https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-predict#mlpredict_function
5433class Predict(Func):
5434    arg_types = {"this": True, "expression": True, "params_struct": False}
5435
5436
5437class Pow(Binary, Func):
5438    _sql_names = ["POWER", "POW"]
5439
5440
5441class PercentileCont(AggFunc):
5442    arg_types = {"this": True, "expression": False}
5443
5444
5445class PercentileDisc(AggFunc):
5446    arg_types = {"this": True, "expression": False}
5447
5448
5449class Quantile(AggFunc):
5450    arg_types = {"this": True, "quantile": True}
5451
5452
5453class ApproxQuantile(Quantile):
5454    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
5455
5456
5457class Rand(Func):
5458    _sql_names = ["RAND", "RANDOM"]
5459    arg_types = {"this": False}
5460
5461
5462class Randn(Func):
5463    arg_types = {"this": False}
5464
5465
5466class RangeN(Func):
5467    arg_types = {"this": True, "expressions": True, "each": False}
5468
5469
5470class ReadCSV(Func):
5471    _sql_names = ["READ_CSV"]
5472    is_var_len_args = True
5473    arg_types = {"this": True, "expressions": False}
5474
5475
5476class Reduce(Func):
5477    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
5478
5479
5480class RegexpExtract(Func):
5481    arg_types = {
5482        "this": True,
5483        "expression": True,
5484        "position": False,
5485        "occurrence": False,
5486        "parameters": False,
5487        "group": False,
5488    }
5489
5490
5491class RegexpReplace(Func):
5492    arg_types = {
5493        "this": True,
5494        "expression": True,
5495        "replacement": False,
5496        "position": False,
5497        "occurrence": False,
5498        "parameters": False,
5499        "modifiers": False,
5500    }
5501
5502
5503class RegexpLike(Binary, Func):
5504    arg_types = {"this": True, "expression": True, "flag": False}
5505
5506
5507class RegexpILike(Binary, Func):
5508    arg_types = {"this": True, "expression": True, "flag": False}
5509
5510
5511# https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.split.html
5512# limit is the number of times a pattern is applied
5513class RegexpSplit(Func):
5514    arg_types = {"this": True, "expression": True, "limit": False}
5515
5516
5517class Repeat(Func):
5518    arg_types = {"this": True, "times": True}
5519
5520
5521# https://learn.microsoft.com/en-us/sql/t-sql/functions/round-transact-sql?view=sql-server-ver16
5522# tsql third argument function == trunctaion if not 0
5523class Round(Func):
5524    arg_types = {"this": True, "decimals": False, "truncate": False}
5525
5526
5527class RowNumber(Func):
5528    arg_types: t.Dict[str, t.Any] = {}
5529
5530
5531class SafeDivide(Func):
5532    arg_types = {"this": True, "expression": True}
5533
5534
5535class SHA(Func):
5536    _sql_names = ["SHA", "SHA1"]
5537
5538
5539class SHA2(Func):
5540    _sql_names = ["SHA2"]
5541    arg_types = {"this": True, "length": False}
5542
5543
5544class Sign(Func):
5545    _sql_names = ["SIGN", "SIGNUM"]
5546
5547
5548class SortArray(Func):
5549    arg_types = {"this": True, "asc": False}
5550
5551
5552class Split(Func):
5553    arg_types = {"this": True, "expression": True, "limit": False}
5554
5555
5556# Start may be omitted in the case of postgres
5557# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
5558class Substring(Func):
5559    arg_types = {"this": True, "start": False, "length": False}
5560
5561
5562class StandardHash(Func):
5563    arg_types = {"this": True, "expression": False}
5564
5565
5566class StartsWith(Func):
5567    _sql_names = ["STARTS_WITH", "STARTSWITH"]
5568    arg_types = {"this": True, "expression": True}
5569
5570
5571class StrPosition(Func):
5572    arg_types = {
5573        "this": True,
5574        "substr": True,
5575        "position": False,
5576        "instance": False,
5577    }
5578
5579
5580class StrToDate(Func):
5581    arg_types = {"this": True, "format": True}
5582
5583
5584class StrToTime(Func):
5585    arg_types = {"this": True, "format": True, "zone": False}
5586
5587
5588# Spark allows unix_timestamp()
5589# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
5590class StrToUnix(Func):
5591    arg_types = {"this": False, "format": False}
5592
5593
5594# https://prestodb.io/docs/current/functions/string.html
5595# https://spark.apache.org/docs/latest/api/sql/index.html#str_to_map
5596class StrToMap(Func):
5597    arg_types = {
5598        "this": True,
5599        "pair_delim": False,
5600        "key_value_delim": False,
5601        "duplicate_resolution_callback": False,
5602    }
5603
5604
5605class NumberToStr(Func):
5606    arg_types = {"this": True, "format": True, "culture": False}
5607
5608
5609class FromBase(Func):
5610    arg_types = {"this": True, "expression": True}
5611
5612
5613class Struct(Func):
5614    arg_types = {"expressions": False}
5615    is_var_len_args = True
5616
5617
5618class StructExtract(Func):
5619    arg_types = {"this": True, "expression": True}
5620
5621
5622# https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver16
5623# https://docs.snowflake.com/en/sql-reference/functions/insert
5624class Stuff(Func):
5625    _sql_names = ["STUFF", "INSERT"]
5626    arg_types = {"this": True, "start": True, "length": True, "expression": True}
5627
5628
5629class Sum(AggFunc):
5630    pass
5631
5632
5633class Sqrt(Func):
5634    pass
5635
5636
5637class Stddev(AggFunc):
5638    pass
5639
5640
5641class StddevPop(AggFunc):
5642    pass
5643
5644
5645class StddevSamp(AggFunc):
5646    pass
5647
5648
5649class TimeToStr(Func):
5650    arg_types = {"this": True, "format": True, "culture": False}
5651
5652
5653class TimeToTimeStr(Func):
5654    pass
5655
5656
5657class TimeToUnix(Func):
5658    pass
5659
5660
5661class TimeStrToDate(Func):
5662    pass
5663
5664
5665class TimeStrToTime(Func):
5666    pass
5667
5668
5669class TimeStrToUnix(Func):
5670    pass
5671
5672
5673class Trim(Func):
5674    arg_types = {
5675        "this": True,
5676        "expression": False,
5677        "position": False,
5678        "collation": False,
5679    }
5680
5681
5682class TsOrDsAdd(Func, TimeUnit):
5683    # return_type is used to correctly cast the arguments of this expression when transpiling it
5684    arg_types = {"this": True, "expression": True, "unit": False, "return_type": False}
5685
5686    @property
5687    def return_type(self) -> DataType:
5688        return DataType.build(self.args.get("return_type") or DataType.Type.DATE)
5689
5690
5691class TsOrDsDiff(Func, TimeUnit):
5692    arg_types = {"this": True, "expression": True, "unit": False}
5693
5694
5695class TsOrDsToDateStr(Func):
5696    pass
5697
5698
5699class TsOrDsToDate(Func):
5700    arg_types = {"this": True, "format": False, "safe": False}
5701
5702
5703class TsOrDsToTime(Func):
5704    pass
5705
5706
5707class TsOrDsToTimestamp(Func):
5708    pass
5709
5710
5711class TsOrDiToDi(Func):
5712    pass
5713
5714
5715class Unhex(Func):
5716    pass
5717
5718
5719# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#unix_date
5720class UnixDate(Func):
5721    pass
5722
5723
5724class UnixToStr(Func):
5725    arg_types = {"this": True, "format": False}
5726
5727
5728# https://prestodb.io/docs/current/functions/datetime.html
5729# presto has weird zone/hours/minutes
5730class UnixToTime(Func):
5731    arg_types = {
5732        "this": True,
5733        "scale": False,
5734        "zone": False,
5735        "hours": False,
5736        "minutes": False,
5737        "format": False,
5738    }
5739
5740    SECONDS = Literal.number(0)
5741    DECIS = Literal.number(1)
5742    CENTIS = Literal.number(2)
5743    MILLIS = Literal.number(3)
5744    DECIMILLIS = Literal.number(4)
5745    CENTIMILLIS = Literal.number(5)
5746    MICROS = Literal.number(6)
5747    DECIMICROS = Literal.number(7)
5748    CENTIMICROS = Literal.number(8)
5749    NANOS = Literal.number(9)
5750
5751
5752class UnixToTimeStr(Func):
5753    pass
5754
5755
5756class TimestampFromParts(Func):
5757    _sql_names = ["TIMESTAMP_FROM_PARTS", "TIMESTAMPFROMPARTS"]
5758    arg_types = {
5759        "year": True,
5760        "month": True,
5761        "day": True,
5762        "hour": True,
5763        "min": True,
5764        "sec": True,
5765        "nano": False,
5766        "zone": False,
5767        "milli": False,
5768    }
5769
5770
5771class Upper(Func):
5772    _sql_names = ["UPPER", "UCASE"]
5773
5774
5775class Corr(Binary, AggFunc):
5776    pass
5777
5778
5779class Variance(AggFunc):
5780    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
5781
5782
5783class VariancePop(AggFunc):
5784    _sql_names = ["VARIANCE_POP", "VAR_POP"]
5785
5786
5787class CovarSamp(Binary, AggFunc):
5788    pass
5789
5790
5791class CovarPop(Binary, AggFunc):
5792    pass
5793
5794
5795class Week(Func):
5796    arg_types = {"this": True, "mode": False}
5797
5798
5799class XMLTable(Func):
5800    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
5801
5802
5803class Year(Func):
5804    pass
5805
5806
5807class Use(Expression):
5808    arg_types = {"this": True, "kind": False}
5809
5810
5811class Merge(Expression):
5812    arg_types = {
5813        "this": True,
5814        "using": True,
5815        "on": True,
5816        "expressions": True,
5817        "with": False,
5818    }
5819
5820
5821class When(Func):
5822    arg_types = {"matched": True, "source": False, "condition": False, "then": True}
5823
5824
5825# https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnextvaluefor.html
5826# https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql?view=sql-server-ver16
5827class NextValueFor(Func):
5828    arg_types = {"this": True, "order": False}
5829
5830
5831def _norm_arg(arg):
5832    return arg.lower() if type(arg) is str else arg
5833
5834
5835ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
5836FUNCTION_BY_NAME = {name: func for func in ALL_FUNCTIONS for name in func.sql_names()}
5837
5838JSON_PATH_PARTS = subclasses(__name__, JSONPathPart, (JSONPathPart,))
5839
5840
5841# Helpers
5842@t.overload
5843def maybe_parse(
5844    sql_or_expression: ExpOrStr,
5845    *,
5846    into: t.Type[E],
5847    dialect: DialectType = None,
5848    prefix: t.Optional[str] = None,
5849    copy: bool = False,
5850    **opts,
5851) -> E: ...
5852
5853
5854@t.overload
5855def maybe_parse(
5856    sql_or_expression: str | E,
5857    *,
5858    into: t.Optional[IntoType] = None,
5859    dialect: DialectType = None,
5860    prefix: t.Optional[str] = None,
5861    copy: bool = False,
5862    **opts,
5863) -> E: ...
5864
5865
5866def maybe_parse(
5867    sql_or_expression: ExpOrStr,
5868    *,
5869    into: t.Optional[IntoType] = None,
5870    dialect: DialectType = None,
5871    prefix: t.Optional[str] = None,
5872    copy: bool = False,
5873    **opts,
5874) -> Expression:
5875    """Gracefully handle a possible string or expression.
5876
5877    Example:
5878        >>> maybe_parse("1")
5879        Literal(this=1, is_string=False)
5880        >>> maybe_parse(to_identifier("x"))
5881        Identifier(this=x, quoted=False)
5882
5883    Args:
5884        sql_or_expression: the SQL code string or an expression
5885        into: the SQLGlot Expression to parse into
5886        dialect: the dialect used to parse the input expressions (in the case that an
5887            input expression is a SQL string).
5888        prefix: a string to prefix the sql with before it gets parsed
5889            (automatically includes a space)
5890        copy: whether to copy the expression.
5891        **opts: other options to use to parse the input expressions (again, in the case
5892            that an input expression is a SQL string).
5893
5894    Returns:
5895        Expression: the parsed or given expression.
5896    """
5897    if isinstance(sql_or_expression, Expression):
5898        if copy:
5899            return sql_or_expression.copy()
5900        return sql_or_expression
5901
5902    if sql_or_expression is None:
5903        raise ParseError("SQL cannot be None")
5904
5905    import sqlglot
5906
5907    sql = str(sql_or_expression)
5908    if prefix:
5909        sql = f"{prefix} {sql}"
5910
5911    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
5912
5913
5914@t.overload
5915def maybe_copy(instance: None, copy: bool = True) -> None: ...
5916
5917
5918@t.overload
5919def maybe_copy(instance: E, copy: bool = True) -> E: ...
5920
5921
5922def maybe_copy(instance, copy=True):
5923    return instance.copy() if copy and instance else instance
5924
5925
5926def _to_s(node: t.Any, verbose: bool = False, level: int = 0) -> str:
5927    """Generate a textual representation of an Expression tree"""
5928    indent = "\n" + ("  " * (level + 1))
5929    delim = f",{indent}"
5930
5931    if isinstance(node, Expression):
5932        args = {k: v for k, v in node.args.items() if (v is not None and v != []) or verbose}
5933
5934        if (node.type or verbose) and not isinstance(node, DataType):
5935            args["_type"] = node.type
5936        if node.comments or verbose:
5937            args["_comments"] = node.comments
5938
5939        if verbose:
5940            args["_id"] = id(node)
5941
5942        # Inline leaves for a more compact representation
5943        if node.is_leaf():
5944            indent = ""
5945            delim = ", "
5946
5947        items = delim.join([f"{k}={_to_s(v, verbose, level + 1)}" for k, v in args.items()])
5948        return f"{node.__class__.__name__}({indent}{items})"
5949
5950    if isinstance(node, list):
5951        items = delim.join(_to_s(i, verbose, level + 1) for i in node)
5952        items = f"{indent}{items}" if items else ""
5953        return f"[{items}]"
5954
5955    # Indent multiline strings to match the current level
5956    return indent.join(textwrap.dedent(str(node).strip("\n")).splitlines())
5957
5958
5959def _is_wrong_expression(expression, into):
5960    return isinstance(expression, Expression) and not isinstance(expression, into)
5961
5962
5963def _apply_builder(
5964    expression,
5965    instance,
5966    arg,
5967    copy=True,
5968    prefix=None,
5969    into=None,
5970    dialect=None,
5971    into_arg="this",
5972    **opts,
5973):
5974    if _is_wrong_expression(expression, into):
5975        expression = into(**{into_arg: expression})
5976    instance = maybe_copy(instance, copy)
5977    expression = maybe_parse(
5978        sql_or_expression=expression,
5979        prefix=prefix,
5980        into=into,
5981        dialect=dialect,
5982        **opts,
5983    )
5984    instance.set(arg, expression)
5985    return instance
5986
5987
5988def _apply_child_list_builder(
5989    *expressions,
5990    instance,
5991    arg,
5992    append=True,
5993    copy=True,
5994    prefix=None,
5995    into=None,
5996    dialect=None,
5997    properties=None,
5998    **opts,
5999):
6000    instance = maybe_copy(instance, copy)
6001    parsed = []
6002    for expression in expressions:
6003        if expression is not None:
6004            if _is_wrong_expression(expression, into):
6005                expression = into(expressions=[expression])
6006
6007            expression = maybe_parse(
6008                expression,
6009                into=into,
6010                dialect=dialect,
6011                prefix=prefix,
6012                **opts,
6013            )
6014            parsed.extend(expression.expressions)
6015
6016    existing = instance.args.get(arg)
6017    if append and existing:
6018        parsed = existing.expressions + parsed
6019
6020    child = into(expressions=parsed)
6021    for k, v in (properties or {}).items():
6022        child.set(k, v)
6023    instance.set(arg, child)
6024
6025    return instance
6026
6027
6028def _apply_list_builder(
6029    *expressions,
6030    instance,
6031    arg,
6032    append=True,
6033    copy=True,
6034    prefix=None,
6035    into=None,
6036    dialect=None,
6037    **opts,
6038):
6039    inst = maybe_copy(instance, copy)
6040
6041    expressions = [
6042        maybe_parse(
6043            sql_or_expression=expression,
6044            into=into,
6045            prefix=prefix,
6046            dialect=dialect,
6047            **opts,
6048        )
6049        for expression in expressions
6050        if expression is not None
6051    ]
6052
6053    existing_expressions = inst.args.get(arg)
6054    if append and existing_expressions:
6055        expressions = existing_expressions + expressions
6056
6057    inst.set(arg, expressions)
6058    return inst
6059
6060
6061def _apply_conjunction_builder(
6062    *expressions,
6063    instance,
6064    arg,
6065    into=None,
6066    append=True,
6067    copy=True,
6068    dialect=None,
6069    **opts,
6070):
6071    expressions = [exp for exp in expressions if exp is not None and exp != ""]
6072    if not expressions:
6073        return instance
6074
6075    inst = maybe_copy(instance, copy)
6076
6077    existing = inst.args.get(arg)
6078    if append and existing is not None:
6079        expressions = [existing.this if into else existing] + list(expressions)
6080
6081    node = and_(*expressions, dialect=dialect, copy=copy, **opts)
6082
6083    inst.set(arg, into(this=node) if into else node)
6084    return inst
6085
6086
6087def _apply_cte_builder(
6088    instance: E,
6089    alias: ExpOrStr,
6090    as_: ExpOrStr,
6091    recursive: t.Optional[bool] = None,
6092    append: bool = True,
6093    dialect: DialectType = None,
6094    copy: bool = True,
6095    **opts,
6096) -> E:
6097    alias_expression = maybe_parse(alias, dialect=dialect, into=TableAlias, **opts)
6098    as_expression = maybe_parse(as_, dialect=dialect, **opts)
6099    cte = CTE(this=as_expression, alias=alias_expression)
6100    return _apply_child_list_builder(
6101        cte,
6102        instance=instance,
6103        arg="with",
6104        append=append,
6105        copy=copy,
6106        into=With,
6107        properties={"recursive": recursive or False},
6108    )
6109
6110
6111def _combine(
6112    expressions: t.Sequence[t.Optional[ExpOrStr]],
6113    operator: t.Type[Connector],
6114    dialect: DialectType = None,
6115    copy: bool = True,
6116    **opts,
6117) -> Expression:
6118    conditions = [
6119        condition(expression, dialect=dialect, copy=copy, **opts)
6120        for expression in expressions
6121        if expression is not None
6122    ]
6123
6124    this, *rest = conditions
6125    if rest:
6126        this = _wrap(this, Connector)
6127    for expression in rest:
6128        this = operator(this=this, expression=_wrap(expression, Connector))
6129
6130    return this
6131
6132
6133def _wrap(expression: E, kind: t.Type[Expression]) -> E | Paren:
6134    return Paren(this=expression) if isinstance(expression, kind) else expression
6135
6136
6137def union(
6138    left: ExpOrStr,
6139    right: ExpOrStr,
6140    distinct: bool = True,
6141    dialect: DialectType = None,
6142    copy: bool = True,
6143    **opts,
6144) -> Union:
6145    """
6146    Initializes a syntax tree from one UNION expression.
6147
6148    Example:
6149        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
6150        'SELECT * FROM foo UNION SELECT * FROM bla'
6151
6152    Args:
6153        left: the SQL code string corresponding to the left-hand side.
6154            If an `Expression` instance is passed, it will be used as-is.
6155        right: the SQL code string corresponding to the right-hand side.
6156            If an `Expression` instance is passed, it will be used as-is.
6157        distinct: set the DISTINCT flag if and only if this is true.
6158        dialect: the dialect used to parse the input expression.
6159        copy: whether to copy the expression.
6160        opts: other options to use to parse the input expressions.
6161
6162    Returns:
6163        The new Union instance.
6164    """
6165    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
6166    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
6167
6168    return Union(this=left, expression=right, distinct=distinct)
6169
6170
6171def intersect(
6172    left: ExpOrStr,
6173    right: ExpOrStr,
6174    distinct: bool = True,
6175    dialect: DialectType = None,
6176    copy: bool = True,
6177    **opts,
6178) -> Intersect:
6179    """
6180    Initializes a syntax tree from one INTERSECT expression.
6181
6182    Example:
6183        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
6184        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
6185
6186    Args:
6187        left: the SQL code string corresponding to the left-hand side.
6188            If an `Expression` instance is passed, it will be used as-is.
6189        right: the SQL code string corresponding to the right-hand side.
6190            If an `Expression` instance is passed, it will be used as-is.
6191        distinct: set the DISTINCT flag if and only if this is true.
6192        dialect: the dialect used to parse the input expression.
6193        copy: whether to copy the expression.
6194        opts: other options to use to parse the input expressions.
6195
6196    Returns:
6197        The new Intersect instance.
6198    """
6199    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
6200    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
6201
6202    return Intersect(this=left, expression=right, distinct=distinct)
6203
6204
6205def except_(
6206    left: ExpOrStr,
6207    right: ExpOrStr,
6208    distinct: bool = True,
6209    dialect: DialectType = None,
6210    copy: bool = True,
6211    **opts,
6212) -> Except:
6213    """
6214    Initializes a syntax tree from one EXCEPT expression.
6215
6216    Example:
6217        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
6218        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
6219
6220    Args:
6221        left: the SQL code string corresponding to the left-hand side.
6222            If an `Expression` instance is passed, it will be used as-is.
6223        right: the SQL code string corresponding to the right-hand side.
6224            If an `Expression` instance is passed, it will be used as-is.
6225        distinct: set the DISTINCT flag if and only if this is true.
6226        dialect: the dialect used to parse the input expression.
6227        copy: whether to copy the expression.
6228        opts: other options to use to parse the input expressions.
6229
6230    Returns:
6231        The new Except instance.
6232    """
6233    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
6234    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
6235
6236    return Except(this=left, expression=right, distinct=distinct)
6237
6238
6239def select(*expressions: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
6240    """
6241    Initializes a syntax tree from one or multiple SELECT expressions.
6242
6243    Example:
6244        >>> select("col1", "col2").from_("tbl").sql()
6245        'SELECT col1, col2 FROM tbl'
6246
6247    Args:
6248        *expressions: the SQL code string to parse as the expressions of a
6249            SELECT statement. If an Expression instance is passed, this is used as-is.
6250        dialect: the dialect used to parse the input expressions (in the case that an
6251            input expression is a SQL string).
6252        **opts: other options to use to parse the input expressions (again, in the case
6253            that an input expression is a SQL string).
6254
6255    Returns:
6256        Select: the syntax tree for the SELECT statement.
6257    """
6258    return Select().select(*expressions, dialect=dialect, **opts)
6259
6260
6261def from_(expression: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
6262    """
6263    Initializes a syntax tree from a FROM expression.
6264
6265    Example:
6266        >>> from_("tbl").select("col1", "col2").sql()
6267        'SELECT col1, col2 FROM tbl'
6268
6269    Args:
6270        *expression: the SQL code string to parse as the FROM expressions of a
6271            SELECT statement. If an Expression instance is passed, this is used as-is.
6272        dialect: the dialect used to parse the input expression (in the case that the
6273            input expression is a SQL string).
6274        **opts: other options to use to parse the input expressions (again, in the case
6275            that the input expression is a SQL string).
6276
6277    Returns:
6278        Select: the syntax tree for the SELECT statement.
6279    """
6280    return Select().from_(expression, dialect=dialect, **opts)
6281
6282
6283def update(
6284    table: str | Table,
6285    properties: dict,
6286    where: t.Optional[ExpOrStr] = None,
6287    from_: t.Optional[ExpOrStr] = None,
6288    dialect: DialectType = None,
6289    **opts,
6290) -> Update:
6291    """
6292    Creates an update statement.
6293
6294    Example:
6295        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
6296        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
6297
6298    Args:
6299        *properties: dictionary of properties to set which are
6300            auto converted to sql objects eg None -> NULL
6301        where: sql conditional parsed into a WHERE statement
6302        from_: sql statement parsed into a FROM statement
6303        dialect: the dialect used to parse the input expressions.
6304        **opts: other options to use to parse the input expressions.
6305
6306    Returns:
6307        Update: the syntax tree for the UPDATE statement.
6308    """
6309    update_expr = Update(this=maybe_parse(table, into=Table, dialect=dialect))
6310    update_expr.set(
6311        "expressions",
6312        [
6313            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
6314            for k, v in properties.items()
6315        ],
6316    )
6317    if from_:
6318        update_expr.set(
6319            "from",
6320            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
6321        )
6322    if isinstance(where, Condition):
6323        where = Where(this=where)
6324    if where:
6325        update_expr.set(
6326            "where",
6327            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
6328        )
6329    return update_expr
6330
6331
6332def delete(
6333    table: ExpOrStr,
6334    where: t.Optional[ExpOrStr] = None,
6335    returning: t.Optional[ExpOrStr] = None,
6336    dialect: DialectType = None,
6337    **opts,
6338) -> Delete:
6339    """
6340    Builds a delete statement.
6341
6342    Example:
6343        >>> delete("my_table", where="id > 1").sql()
6344        'DELETE FROM my_table WHERE id > 1'
6345
6346    Args:
6347        where: sql conditional parsed into a WHERE statement
6348        returning: sql conditional parsed into a RETURNING statement
6349        dialect: the dialect used to parse the input expressions.
6350        **opts: other options to use to parse the input expressions.
6351
6352    Returns:
6353        Delete: the syntax tree for the DELETE statement.
6354    """
6355    delete_expr = Delete().delete(table, dialect=dialect, copy=False, **opts)
6356    if where:
6357        delete_expr = delete_expr.where(where, dialect=dialect, copy=False, **opts)
6358    if returning:
6359        delete_expr = t.cast(
6360            Delete, delete_expr.returning(returning, dialect=dialect, copy=False, **opts)
6361        )
6362    return delete_expr
6363
6364
6365def insert(
6366    expression: ExpOrStr,
6367    into: ExpOrStr,
6368    columns: t.Optional[t.Sequence[str | Identifier]] = None,
6369    overwrite: t.Optional[bool] = None,
6370    returning: t.Optional[ExpOrStr] = None,
6371    dialect: DialectType = None,
6372    copy: bool = True,
6373    **opts,
6374) -> Insert:
6375    """
6376    Builds an INSERT statement.
6377
6378    Example:
6379        >>> insert("VALUES (1, 2, 3)", "tbl").sql()
6380        'INSERT INTO tbl VALUES (1, 2, 3)'
6381
6382    Args:
6383        expression: the sql string or expression of the INSERT statement
6384        into: the tbl to insert data to.
6385        columns: optionally the table's column names.
6386        overwrite: whether to INSERT OVERWRITE or not.
6387        returning: sql conditional parsed into a RETURNING statement
6388        dialect: the dialect used to parse the input expressions.
6389        copy: whether to copy the expression.
6390        **opts: other options to use to parse the input expressions.
6391
6392    Returns:
6393        Insert: the syntax tree for the INSERT statement.
6394    """
6395    expr = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
6396    this: Table | Schema = maybe_parse(into, into=Table, dialect=dialect, copy=copy, **opts)
6397
6398    if columns:
6399        this = Schema(this=this, expressions=[to_identifier(c, copy=copy) for c in columns])
6400
6401    insert = Insert(this=this, expression=expr, overwrite=overwrite)
6402
6403    if returning:
6404        insert = t.cast(Insert, insert.returning(returning, dialect=dialect, copy=False, **opts))
6405
6406    return insert
6407
6408
6409def condition(
6410    expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
6411) -> Condition:
6412    """
6413    Initialize a logical condition expression.
6414
6415    Example:
6416        >>> condition("x=1").sql()
6417        'x = 1'
6418
6419        This is helpful for composing larger logical syntax trees:
6420        >>> where = condition("x=1")
6421        >>> where = where.and_("y=1")
6422        >>> Select().from_("tbl").select("*").where(where).sql()
6423        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
6424
6425    Args:
6426        *expression: the SQL code string to parse.
6427            If an Expression instance is passed, this is used as-is.
6428        dialect: the dialect used to parse the input expression (in the case that the
6429            input expression is a SQL string).
6430        copy: Whether to copy `expression` (only applies to expressions).
6431        **opts: other options to use to parse the input expressions (again, in the case
6432            that the input expression is a SQL string).
6433
6434    Returns:
6435        The new Condition instance
6436    """
6437    return maybe_parse(
6438        expression,
6439        into=Condition,
6440        dialect=dialect,
6441        copy=copy,
6442        **opts,
6443    )
6444
6445
6446def and_(
6447    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
6448) -> Condition:
6449    """
6450    Combine multiple conditions with an AND logical operator.
6451
6452    Example:
6453        >>> and_("x=1", and_("y=1", "z=1")).sql()
6454        'x = 1 AND (y = 1 AND z = 1)'
6455
6456    Args:
6457        *expressions: the SQL code strings to parse.
6458            If an Expression instance is passed, this is used as-is.
6459        dialect: the dialect used to parse the input expression.
6460        copy: whether to copy `expressions` (only applies to Expressions).
6461        **opts: other options to use to parse the input expressions.
6462
6463    Returns:
6464        And: the new condition
6465    """
6466    return t.cast(Condition, _combine(expressions, And, dialect, copy=copy, **opts))
6467
6468
6469def or_(
6470    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
6471) -> Condition:
6472    """
6473    Combine multiple conditions with an OR logical operator.
6474
6475    Example:
6476        >>> or_("x=1", or_("y=1", "z=1")).sql()
6477        'x = 1 OR (y = 1 OR z = 1)'
6478
6479    Args:
6480        *expressions: the SQL code strings to parse.
6481            If an Expression instance is passed, this is used as-is.
6482        dialect: the dialect used to parse the input expression.
6483        copy: whether to copy `expressions` (only applies to Expressions).
6484        **opts: other options to use to parse the input expressions.
6485
6486    Returns:
6487        Or: the new condition
6488    """
6489    return t.cast(Condition, _combine(expressions, Or, dialect, copy=copy, **opts))
6490
6491
6492def not_(expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts) -> Not:
6493    """
6494    Wrap a condition with a NOT operator.
6495
6496    Example:
6497        >>> not_("this_suit='black'").sql()
6498        "NOT this_suit = 'black'"
6499
6500    Args:
6501        expression: the SQL code string to parse.
6502            If an Expression instance is passed, this is used as-is.
6503        dialect: the dialect used to parse the input expression.
6504        copy: whether to copy the expression or not.
6505        **opts: other options to use to parse the input expressions.
6506
6507    Returns:
6508        The new condition.
6509    """
6510    this = condition(
6511        expression,
6512        dialect=dialect,
6513        copy=copy,
6514        **opts,
6515    )
6516    return Not(this=_wrap(this, Connector))
6517
6518
6519def paren(expression: ExpOrStr, copy: bool = True) -> Paren:
6520    """
6521    Wrap an expression in parentheses.
6522
6523    Example:
6524        >>> paren("5 + 3").sql()
6525        '(5 + 3)'
6526
6527    Args:
6528        expression: the SQL code string to parse.
6529            If an Expression instance is passed, this is used as-is.
6530        copy: whether to copy the expression or not.
6531
6532    Returns:
6533        The wrapped expression.
6534    """
6535    return Paren(this=maybe_parse(expression, copy=copy))
6536
6537
6538SAFE_IDENTIFIER_RE: t.Pattern[str] = re.compile(r"^[_a-zA-Z][\w]*$")
6539
6540
6541@t.overload
6542def to_identifier(name: None, quoted: t.Optional[bool] = None, copy: bool = True) -> None: ...
6543
6544
6545@t.overload
6546def to_identifier(
6547    name: str | Identifier, quoted: t.Optional[bool] = None, copy: bool = True
6548) -> Identifier: ...
6549
6550
6551def to_identifier(name, quoted=None, copy=True):
6552    """Builds an identifier.
6553
6554    Args:
6555        name: The name to turn into an identifier.
6556        quoted: Whether to force quote the identifier.
6557        copy: Whether to copy name if it's an Identifier.
6558
6559    Returns:
6560        The identifier ast node.
6561    """
6562
6563    if name is None:
6564        return None
6565
6566    if isinstance(name, Identifier):
6567        identifier = maybe_copy(name, copy)
6568    elif isinstance(name, str):
6569        identifier = Identifier(
6570            this=name,
6571            quoted=not SAFE_IDENTIFIER_RE.match(name) if quoted is None else quoted,
6572        )
6573    else:
6574        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
6575    return identifier
6576
6577
6578def parse_identifier(name: str | Identifier, dialect: DialectType = None) -> Identifier:
6579    """
6580    Parses a given string into an identifier.
6581
6582    Args:
6583        name: The name to parse into an identifier.
6584        dialect: The dialect to parse against.
6585
6586    Returns:
6587        The identifier ast node.
6588    """
6589    try:
6590        expression = maybe_parse(name, dialect=dialect, into=Identifier)
6591    except ParseError:
6592        expression = to_identifier(name)
6593
6594    return expression
6595
6596
6597INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
6598
6599
6600def to_interval(interval: str | Literal) -> Interval:
6601    """Builds an interval expression from a string like '1 day' or '5 months'."""
6602    if isinstance(interval, Literal):
6603        if not interval.is_string:
6604            raise ValueError("Invalid interval string.")
6605
6606        interval = interval.this
6607
6608    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
6609
6610    if not interval_parts:
6611        raise ValueError("Invalid interval string.")
6612
6613    return Interval(
6614        this=Literal.string(interval_parts.group(1)),
6615        unit=Var(this=interval_parts.group(2).upper()),
6616    )
6617
6618
6619@t.overload
6620def to_table(sql_path: str | Table, **kwargs) -> Table: ...
6621
6622
6623@t.overload
6624def to_table(sql_path: None, **kwargs) -> None: ...
6625
6626
6627def to_table(
6628    sql_path: t.Optional[str | Table], dialect: DialectType = None, copy: bool = True, **kwargs
6629) -> t.Optional[Table]:
6630    """
6631    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
6632    If a table is passed in then that table is returned.
6633
6634    Args:
6635        sql_path: a `[catalog].[schema].[table]` string.
6636        dialect: the source dialect according to which the table name will be parsed.
6637        copy: Whether to copy a table if it is passed in.
6638        kwargs: the kwargs to instantiate the resulting `Table` expression with.
6639
6640    Returns:
6641        A table expression.
6642    """
6643    if sql_path is None or isinstance(sql_path, Table):
6644        return maybe_copy(sql_path, copy=copy)
6645    if not isinstance(sql_path, str):
6646        raise ValueError(f"Invalid type provided for a table: {type(sql_path)}")
6647
6648    table = maybe_parse(sql_path, into=Table, dialect=dialect)
6649    if table:
6650        for k, v in kwargs.items():
6651            table.set(k, v)
6652
6653    return table
6654
6655
6656def to_column(sql_path: str | Column, **kwargs) -> Column:
6657    """
6658    Create a column from a `[table].[column]` sql path. Schema is optional.
6659
6660    If a column is passed in then that column is returned.
6661
6662    Args:
6663        sql_path: `[table].[column]` string
6664    Returns:
6665        Table: A column expression
6666    """
6667    if sql_path is None or isinstance(sql_path, Column):
6668        return sql_path
6669    if not isinstance(sql_path, str):
6670        raise ValueError(f"Invalid type provided for column: {type(sql_path)}")
6671    return column(*reversed(sql_path.split(".")), **kwargs)  # type: ignore
6672
6673
6674def alias_(
6675    expression: ExpOrStr,
6676    alias: t.Optional[str | Identifier],
6677    table: bool | t.Sequence[str | Identifier] = False,
6678    quoted: t.Optional[bool] = None,
6679    dialect: DialectType = None,
6680    copy: bool = True,
6681    **opts,
6682):
6683    """Create an Alias expression.
6684
6685    Example:
6686        >>> alias_('foo', 'bar').sql()
6687        'foo AS bar'
6688
6689        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
6690        '(SELECT 1, 2) AS bar(a, b)'
6691
6692    Args:
6693        expression: the SQL code strings to parse.
6694            If an Expression instance is passed, this is used as-is.
6695        alias: the alias name to use. If the name has
6696            special characters it is quoted.
6697        table: Whether to create a table alias, can also be a list of columns.
6698        quoted: whether to quote the alias
6699        dialect: the dialect used to parse the input expression.
6700        copy: Whether to copy the expression.
6701        **opts: other options to use to parse the input expressions.
6702
6703    Returns:
6704        Alias: the aliased expression
6705    """
6706    exp = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
6707    alias = to_identifier(alias, quoted=quoted)
6708
6709    if table:
6710        table_alias = TableAlias(this=alias)
6711        exp.set("alias", table_alias)
6712
6713        if not isinstance(table, bool):
6714            for column in table:
6715                table_alias.append("columns", to_identifier(column, quoted=quoted))
6716
6717        return exp
6718
6719    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
6720    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
6721    # for the complete Window expression.
6722    #
6723    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
6724
6725    if "alias" in exp.arg_types and not isinstance(exp, Window):
6726        exp.set("alias", alias)
6727        return exp
6728    return Alias(this=exp, alias=alias)
6729
6730
6731def subquery(
6732    expression: ExpOrStr,
6733    alias: t.Optional[Identifier | str] = None,
6734    dialect: DialectType = None,
6735    **opts,
6736) -> Select:
6737    """
6738    Build a subquery expression that's selected from.
6739
6740    Example:
6741        >>> subquery('select x from tbl', 'bar').select('x').sql()
6742        'SELECT x FROM (SELECT x FROM tbl) AS bar'
6743
6744    Args:
6745        expression: the SQL code strings to parse.
6746            If an Expression instance is passed, this is used as-is.
6747        alias: the alias name to use.
6748        dialect: the dialect used to parse the input expression.
6749        **opts: other options to use to parse the input expressions.
6750
6751    Returns:
6752        A new Select instance with the subquery expression included.
6753    """
6754
6755    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias)
6756    return Select().from_(expression, dialect=dialect, **opts)
6757
6758
6759@t.overload
6760def column(
6761    col: str | Identifier,
6762    table: t.Optional[str | Identifier] = None,
6763    db: t.Optional[str | Identifier] = None,
6764    catalog: t.Optional[str | Identifier] = None,
6765    *,
6766    fields: t.Collection[t.Union[str, Identifier]],
6767    quoted: t.Optional[bool] = None,
6768    copy: bool = True,
6769) -> Dot:
6770    pass
6771
6772
6773@t.overload
6774def column(
6775    col: str | Identifier,
6776    table: t.Optional[str | Identifier] = None,
6777    db: t.Optional[str | Identifier] = None,
6778    catalog: t.Optional[str | Identifier] = None,
6779    *,
6780    fields: Lit[None] = None,
6781    quoted: t.Optional[bool] = None,
6782    copy: bool = True,
6783) -> Column:
6784    pass
6785
6786
6787def column(
6788    col,
6789    table=None,
6790    db=None,
6791    catalog=None,
6792    *,
6793    fields=None,
6794    quoted=None,
6795    copy=True,
6796):
6797    """
6798    Build a Column.
6799
6800    Args:
6801        col: Column name.
6802        table: Table name.
6803        db: Database name.
6804        catalog: Catalog name.
6805        fields: Additional fields using dots.
6806        quoted: Whether to force quotes on the column's identifiers.
6807        copy: Whether to copy identifiers if passed in.
6808
6809    Returns:
6810        The new Column instance.
6811    """
6812    this = Column(
6813        this=to_identifier(col, quoted=quoted, copy=copy),
6814        table=to_identifier(table, quoted=quoted, copy=copy),
6815        db=to_identifier(db, quoted=quoted, copy=copy),
6816        catalog=to_identifier(catalog, quoted=quoted, copy=copy),
6817    )
6818
6819    if fields:
6820        this = Dot.build((this, *(to_identifier(field, copy=copy) for field in fields)))
6821    return this
6822
6823
6824def cast(expression: ExpOrStr, to: DATA_TYPE, copy: bool = True, **opts) -> Cast:
6825    """Cast an expression to a data type.
6826
6827    Example:
6828        >>> cast('x + 1', 'int').sql()
6829        'CAST(x + 1 AS INT)'
6830
6831    Args:
6832        expression: The expression to cast.
6833        to: The datatype to cast to.
6834        copy: Whether to copy the supplied expressions.
6835
6836    Returns:
6837        The new Cast instance.
6838    """
6839    expression = maybe_parse(expression, copy=copy, **opts)
6840    data_type = DataType.build(to, copy=copy, **opts)
6841    expression = Cast(this=expression, to=data_type)
6842    expression.type = data_type
6843    return expression
6844
6845
6846def table_(
6847    table: Identifier | str,
6848    db: t.Optional[Identifier | str] = None,
6849    catalog: t.Optional[Identifier | str] = None,
6850    quoted: t.Optional[bool] = None,
6851    alias: t.Optional[Identifier | str] = None,
6852) -> Table:
6853    """Build a Table.
6854
6855    Args:
6856        table: Table name.
6857        db: Database name.
6858        catalog: Catalog name.
6859        quote: Whether to force quotes on the table's identifiers.
6860        alias: Table's alias.
6861
6862    Returns:
6863        The new Table instance.
6864    """
6865    return Table(
6866        this=to_identifier(table, quoted=quoted) if table else None,
6867        db=to_identifier(db, quoted=quoted) if db else None,
6868        catalog=to_identifier(catalog, quoted=quoted) if catalog else None,
6869        alias=TableAlias(this=to_identifier(alias)) if alias else None,
6870    )
6871
6872
6873def values(
6874    values: t.Iterable[t.Tuple[t.Any, ...]],
6875    alias: t.Optional[str] = None,
6876    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
6877) -> Values:
6878    """Build VALUES statement.
6879
6880    Example:
6881        >>> values([(1, '2')]).sql()
6882        "VALUES (1, '2')"
6883
6884    Args:
6885        values: values statements that will be converted to SQL
6886        alias: optional alias
6887        columns: Optional list of ordered column names or ordered dictionary of column names to types.
6888         If either are provided then an alias is also required.
6889
6890    Returns:
6891        Values: the Values expression object
6892    """
6893    if columns and not alias:
6894        raise ValueError("Alias is required when providing columns")
6895
6896    return Values(
6897        expressions=[convert(tup) for tup in values],
6898        alias=(
6899            TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
6900            if columns
6901            else (TableAlias(this=to_identifier(alias)) if alias else None)
6902        ),
6903    )
6904
6905
6906def var(name: t.Optional[ExpOrStr]) -> Var:
6907    """Build a SQL variable.
6908
6909    Example:
6910        >>> repr(var('x'))
6911        'Var(this=x)'
6912
6913        >>> repr(var(column('x', table='y')))
6914        'Var(this=x)'
6915
6916    Args:
6917        name: The name of the var or an expression who's name will become the var.
6918
6919    Returns:
6920        The new variable node.
6921    """
6922    if not name:
6923        raise ValueError("Cannot convert empty name into var.")
6924
6925    if isinstance(name, Expression):
6926        name = name.name
6927    return Var(this=name)
6928
6929
6930def rename_table(old_name: str | Table, new_name: str | Table) -> AlterTable:
6931    """Build ALTER TABLE... RENAME... expression
6932
6933    Args:
6934        old_name: The old name of the table
6935        new_name: The new name of the table
6936
6937    Returns:
6938        Alter table expression
6939    """
6940    old_table = to_table(old_name)
6941    new_table = to_table(new_name)
6942    return AlterTable(
6943        this=old_table,
6944        actions=[
6945            RenameTable(this=new_table),
6946        ],
6947    )
6948
6949
6950def rename_column(
6951    table_name: str | Table,
6952    old_column_name: str | Column,
6953    new_column_name: str | Column,
6954    exists: t.Optional[bool] = None,
6955) -> AlterTable:
6956    """Build ALTER TABLE... RENAME COLUMN... expression
6957
6958    Args:
6959        table_name: Name of the table
6960        old_column: The old name of the column
6961        new_column: The new name of the column
6962        exists: Whether to add the `IF EXISTS` clause
6963
6964    Returns:
6965        Alter table expression
6966    """
6967    table = to_table(table_name)
6968    old_column = to_column(old_column_name)
6969    new_column = to_column(new_column_name)
6970    return AlterTable(
6971        this=table,
6972        actions=[
6973            RenameColumn(this=old_column, to=new_column, exists=exists),
6974        ],
6975    )
6976
6977
6978def convert(value: t.Any, copy: bool = False) -> Expression:
6979    """Convert a python value into an expression object.
6980
6981    Raises an error if a conversion is not possible.
6982
6983    Args:
6984        value: A python object.
6985        copy: Whether to copy `value` (only applies to Expressions and collections).
6986
6987    Returns:
6988        Expression: the equivalent expression object.
6989    """
6990    if isinstance(value, Expression):
6991        return maybe_copy(value, copy)
6992    if isinstance(value, str):
6993        return Literal.string(value)
6994    if isinstance(value, bool):
6995        return Boolean(this=value)
6996    if value is None or (isinstance(value, float) and math.isnan(value)):
6997        return null()
6998    if isinstance(value, numbers.Number):
6999        return Literal.number(value)
7000    if isinstance(value, bytes):
7001        return HexString(this=value.hex())
7002    if isinstance(value, datetime.datetime):
7003        datetime_literal = Literal.string(
7004            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat(
7005                sep=" "
7006            )
7007        )
7008        return TimeStrToTime(this=datetime_literal)
7009    if isinstance(value, datetime.date):
7010        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
7011        return DateStrToDate(this=date_literal)
7012    if isinstance(value, tuple):
7013        if hasattr(value, "_fields"):
7014            return Struct(
7015                expressions=[
7016                    PropertyEQ(
7017                        this=to_identifier(k), expression=convert(getattr(value, k), copy=copy)
7018                    )
7019                    for k in value._fields
7020                ]
7021            )
7022        return Tuple(expressions=[convert(v, copy=copy) for v in value])
7023    if isinstance(value, list):
7024        return Array(expressions=[convert(v, copy=copy) for v in value])
7025    if isinstance(value, dict):
7026        return Map(
7027            keys=Array(expressions=[convert(k, copy=copy) for k in value]),
7028            values=Array(expressions=[convert(v, copy=copy) for v in value.values()]),
7029        )
7030    if hasattr(value, "__dict__"):
7031        return Struct(
7032            expressions=[
7033                PropertyEQ(this=to_identifier(k), expression=convert(v, copy=copy))
7034                for k, v in value.__dict__.items()
7035            ]
7036        )
7037    raise ValueError(f"Cannot convert {value}")
7038
7039
7040def replace_children(expression: Expression, fun: t.Callable, *args, **kwargs) -> None:
7041    """
7042    Replace children of an expression with the result of a lambda fun(child) -> exp.
7043    """
7044    for k, v in tuple(expression.args.items()):
7045        is_list_arg = type(v) is list
7046
7047        child_nodes = v if is_list_arg else [v]
7048        new_child_nodes = []
7049
7050        for cn in child_nodes:
7051            if isinstance(cn, Expression):
7052                for child_node in ensure_collection(fun(cn, *args, **kwargs)):
7053                    new_child_nodes.append(child_node)
7054            else:
7055                new_child_nodes.append(cn)
7056
7057        expression.set(k, new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0))
7058
7059
7060def replace_tree(
7061    expression: Expression,
7062    fun: t.Callable,
7063    prune: t.Optional[t.Callable[[Expression], bool]] = None,
7064) -> Expression:
7065    """
7066    Replace an entire tree with the result of function calls on each node.
7067
7068    This will be traversed in reverse dfs, so leaves first.
7069    If new nodes are created as a result of function calls, they will also be traversed.
7070    """
7071    stack = list(expression.dfs(prune=prune))
7072
7073    while stack:
7074        node = stack.pop()
7075        new_node = fun(node)
7076
7077        if new_node is not node:
7078            node.replace(new_node)
7079
7080            if isinstance(new_node, Expression):
7081                stack.append(new_node)
7082
7083    return new_node
7084
7085
7086def column_table_names(expression: Expression, exclude: str = "") -> t.Set[str]:
7087    """
7088    Return all table names referenced through columns in an expression.
7089
7090    Example:
7091        >>> import sqlglot
7092        >>> sorted(column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e")))
7093        ['a', 'c']
7094
7095    Args:
7096        expression: expression to find table names.
7097        exclude: a table name to exclude
7098
7099    Returns:
7100        A list of unique names.
7101    """
7102    return {
7103        table
7104        for table in (column.table for column in expression.find_all(Column))
7105        if table and table != exclude
7106    }
7107
7108
7109def table_name(table: Table | str, dialect: DialectType = None, identify: bool = False) -> str:
7110    """Get the full name of a table as a string.
7111
7112    Args:
7113        table: Table expression node or string.
7114        dialect: The dialect to generate the table name for.
7115        identify: Determines when an identifier should be quoted. Possible values are:
7116            False (default): Never quote, except in cases where it's mandatory by the dialect.
7117            True: Always quote.
7118
7119    Examples:
7120        >>> from sqlglot import exp, parse_one
7121        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
7122        'a.b.c'
7123
7124    Returns:
7125        The table name.
7126    """
7127
7128    table = maybe_parse(table, into=Table, dialect=dialect)
7129
7130    if not table:
7131        raise ValueError(f"Cannot parse {table}")
7132
7133    return ".".join(
7134        (
7135            part.sql(dialect=dialect, identify=True, copy=False)
7136            if identify or not SAFE_IDENTIFIER_RE.match(part.name)
7137            else part.name
7138        )
7139        for part in table.parts
7140    )
7141
7142
7143def normalize_table_name(table: str | Table, dialect: DialectType = None, copy: bool = True) -> str:
7144    """Returns a case normalized table name without quotes.
7145
7146    Args:
7147        table: the table to normalize
7148        dialect: the dialect to use for normalization rules
7149        copy: whether to copy the expression.
7150
7151    Examples:
7152        >>> normalize_table_name("`A-B`.c", dialect="bigquery")
7153        'A-B.c'
7154    """
7155    from sqlglot.optimizer.normalize_identifiers import normalize_identifiers
7156
7157    return ".".join(
7158        p.name
7159        for p in normalize_identifiers(
7160            to_table(table, dialect=dialect, copy=copy), dialect=dialect
7161        ).parts
7162    )
7163
7164
7165def replace_tables(
7166    expression: E, mapping: t.Dict[str, str], dialect: DialectType = None, copy: bool = True
7167) -> E:
7168    """Replace all tables in expression according to the mapping.
7169
7170    Args:
7171        expression: expression node to be transformed and replaced.
7172        mapping: mapping of table names.
7173        dialect: the dialect of the mapping table
7174        copy: whether to copy the expression.
7175
7176    Examples:
7177        >>> from sqlglot import exp, parse_one
7178        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
7179        'SELECT * FROM c /* a.b */'
7180
7181    Returns:
7182        The mapped expression.
7183    """
7184
7185    mapping = {normalize_table_name(k, dialect=dialect): v for k, v in mapping.items()}
7186
7187    def _replace_tables(node: Expression) -> Expression:
7188        if isinstance(node, Table):
7189            original = normalize_table_name(node, dialect=dialect)
7190            new_name = mapping.get(original)
7191
7192            if new_name:
7193                table = to_table(
7194                    new_name,
7195                    **{k: v for k, v in node.args.items() if k not in TABLE_PARTS},
7196                    dialect=dialect,
7197                )
7198                table.add_comments([original])
7199                return table
7200        return node
7201
7202    return expression.transform(_replace_tables, copy=copy)  # type: ignore
7203
7204
7205def replace_placeholders(expression: Expression, *args, **kwargs) -> Expression:
7206    """Replace placeholders in an expression.
7207
7208    Args:
7209        expression: expression node to be transformed and replaced.
7210        args: positional names that will substitute unnamed placeholders in the given order.
7211        kwargs: keyword arguments that will substitute named placeholders.
7212
7213    Examples:
7214        >>> from sqlglot import exp, parse_one
7215        >>> replace_placeholders(
7216        ...     parse_one("select * from :tbl where ? = ?"),
7217        ...     exp.to_identifier("str_col"), "b", tbl=exp.to_identifier("foo")
7218        ... ).sql()
7219        "SELECT * FROM foo WHERE str_col = 'b'"
7220
7221    Returns:
7222        The mapped expression.
7223    """
7224
7225    def _replace_placeholders(node: Expression, args, **kwargs) -> Expression:
7226        if isinstance(node, Placeholder):
7227            if node.name:
7228                new_name = kwargs.get(node.name)
7229                if new_name is not None:
7230                    return convert(new_name)
7231            else:
7232                try:
7233                    return convert(next(args))
7234                except StopIteration:
7235                    pass
7236        return node
7237
7238    return expression.transform(_replace_placeholders, iter(args), **kwargs)
7239
7240
7241def expand(
7242    expression: Expression,
7243    sources: t.Dict[str, Query],
7244    dialect: DialectType = None,
7245    copy: bool = True,
7246) -> Expression:
7247    """Transforms an expression by expanding all referenced sources into subqueries.
7248
7249    Examples:
7250        >>> from sqlglot import parse_one
7251        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
7252        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
7253
7254        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y"), "y": parse_one("select * from z")}).sql()
7255        'SELECT * FROM (SELECT * FROM (SELECT * FROM z) AS y /* source: y */) AS z /* source: x */'
7256
7257    Args:
7258        expression: The expression to expand.
7259        sources: A dictionary of name to Queries.
7260        dialect: The dialect of the sources dict.
7261        copy: Whether to copy the expression during transformation. Defaults to True.
7262
7263    Returns:
7264        The transformed expression.
7265    """
7266    sources = {normalize_table_name(k, dialect=dialect): v for k, v in sources.items()}
7267
7268    def _expand(node: Expression):
7269        if isinstance(node, Table):
7270            name = normalize_table_name(node, dialect=dialect)
7271            source = sources.get(name)
7272            if source:
7273                subquery = source.subquery(node.alias or name)
7274                subquery.comments = [f"source: {name}"]
7275                return subquery.transform(_expand, copy=False)
7276        return node
7277
7278    return expression.transform(_expand, copy=copy)
7279
7280
7281def func(name: str, *args, copy: bool = True, dialect: DialectType = None, **kwargs) -> Func:
7282    """
7283    Returns a Func expression.
7284
7285    Examples:
7286        >>> func("abs", 5).sql()
7287        'ABS(5)'
7288
7289        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
7290        'CAST(5 AS DOUBLE)'
7291
7292    Args:
7293        name: the name of the function to build.
7294        args: the args used to instantiate the function of interest.
7295        copy: whether to copy the argument expressions.
7296        dialect: the source dialect.
7297        kwargs: the kwargs used to instantiate the function of interest.
7298
7299    Note:
7300        The arguments `args` and `kwargs` are mutually exclusive.
7301
7302    Returns:
7303        An instance of the function of interest, or an anonymous function, if `name` doesn't
7304        correspond to an existing `sqlglot.expressions.Func` class.
7305    """
7306    if args and kwargs:
7307        raise ValueError("Can't use both args and kwargs to instantiate a function.")
7308
7309    from sqlglot.dialects.dialect import Dialect
7310
7311    dialect = Dialect.get_or_raise(dialect)
7312
7313    converted: t.List[Expression] = [maybe_parse(arg, dialect=dialect, copy=copy) for arg in args]
7314    kwargs = {key: maybe_parse(value, dialect=dialect, copy=copy) for key, value in kwargs.items()}
7315
7316    constructor = dialect.parser_class.FUNCTIONS.get(name.upper())
7317    if constructor:
7318        if converted:
7319            if "dialect" in constructor.__code__.co_varnames:
7320                function = constructor(converted, dialect=dialect)
7321            else:
7322                function = constructor(converted)
7323        elif constructor.__name__ == "from_arg_list":
7324            function = constructor.__self__(**kwargs)  # type: ignore
7325        else:
7326            constructor = FUNCTION_BY_NAME.get(name.upper())
7327            if constructor:
7328                function = constructor(**kwargs)
7329            else:
7330                raise ValueError(
7331                    f"Unable to convert '{name}' into a Func. Either manually construct "
7332                    "the Func expression of interest or parse the function call."
7333                )
7334    else:
7335        kwargs = kwargs or {"expressions": converted}
7336        function = Anonymous(this=name, **kwargs)
7337
7338    for error_message in function.error_messages(converted):
7339        raise ValueError(error_message)
7340
7341    return function
7342
7343
7344def case(
7345    expression: t.Optional[ExpOrStr] = None,
7346    **opts,
7347) -> Case:
7348    """
7349    Initialize a CASE statement.
7350
7351    Example:
7352        case().when("a = 1", "foo").else_("bar")
7353
7354    Args:
7355        expression: Optionally, the input expression (not all dialects support this)
7356        **opts: Extra keyword arguments for parsing `expression`
7357    """
7358    if expression is not None:
7359        this = maybe_parse(expression, **opts)
7360    else:
7361        this = None
7362    return Case(this=this, ifs=[])
7363
7364
7365def cast_unless(
7366    expression: ExpOrStr,
7367    to: DATA_TYPE,
7368    *types: DATA_TYPE,
7369    **opts: t.Any,
7370) -> Expression | Cast:
7371    """
7372    Cast an expression to a data type unless it is a specified type.
7373
7374    Args:
7375        expression: The expression to cast.
7376        to: The data type to cast to.
7377        **types: The types to exclude from casting.
7378        **opts: Extra keyword arguments for parsing `expression`
7379    """
7380    expr = maybe_parse(expression, **opts)
7381    if expr.is_type(*types):
7382        return expr
7383    return cast(expr, to, **opts)
7384
7385
7386def array(
7387    *expressions: ExpOrStr, copy: bool = True, dialect: DialectType = None, **kwargs
7388) -> Array:
7389    """
7390    Returns an array.
7391
7392    Examples:
7393        >>> array(1, 'x').sql()
7394        'ARRAY(1, x)'
7395
7396    Args:
7397        expressions: the expressions to add to the array.
7398        copy: whether to copy the argument expressions.
7399        dialect: the source dialect.
7400        kwargs: the kwargs used to instantiate the function of interest.
7401
7402    Returns:
7403        An array expression.
7404    """
7405    return Array(
7406        expressions=[
7407            maybe_parse(expression, copy=copy, dialect=dialect, **kwargs)
7408            for expression in expressions
7409        ]
7410    )
7411
7412
7413def tuple_(
7414    *expressions: ExpOrStr, copy: bool = True, dialect: DialectType = None, **kwargs
7415) -> Tuple:
7416    """
7417    Returns an tuple.
7418
7419    Examples:
7420        >>> tuple_(1, 'x').sql()
7421        '(1, x)'
7422
7423    Args:
7424        expressions: the expressions to add to the tuple.
7425        copy: whether to copy the argument expressions.
7426        dialect: the source dialect.
7427        kwargs: the kwargs used to instantiate the function of interest.
7428
7429    Returns:
7430        A tuple expression.
7431    """
7432    return Tuple(
7433        expressions=[
7434            maybe_parse(expression, copy=copy, dialect=dialect, **kwargs)
7435            for expression in expressions
7436        ]
7437    )
7438
7439
7440def true() -> Boolean:
7441    """
7442    Returns a true Boolean expression.
7443    """
7444    return Boolean(this=True)
7445
7446
7447def false() -> Boolean:
7448    """
7449    Returns a false Boolean expression.
7450    """
7451    return Boolean(this=False)
7452
7453
7454def null() -> Null:
7455    """
7456    Returns a Null expression.
7457    """
7458    return Null()
7459
7460
7461NONNULL_CONSTANTS = (
7462    Literal,
7463    Boolean,
7464)
7465
7466CONSTANTS = (
7467    Literal,
7468    Boolean,
7469    Null,
7470)